php – MySQL查询和子查询

发布时间：2020-12-13 17:48:54 所属栏目：PHP教程来源：网络整理

导读：我有这样的查询： SELECT SUM(price) AS GINTO,( (SELECT COUNT(price)*9.99 FROM sms_logs WHERE price = '200000')+ (SELECT COUNT(price)*3.99 FROM sms_logs WHERE price = '60000')+ (SELECT COUNT(price)*1.99 FROM sms_logs WHERE price = '24000')+

我有这样的查询：

SELECT SUM(price) AS GINTO,(
   (SELECT COUNT(price)*9.99 FROM sms_logs WHERE price = '200000')+
   (SELECT COUNT(price)*3.99 FROM sms_logs WHERE price = '60000')+
   (SELECT COUNT(price)*1.99 FROM sms_logs WHERE price = '24000')+
   (SELECT COUNT(price)*0.99 FROM sms_logs WHERE price = '11000')
) AS USD,DATE_FORMAT(DATE,'%Y-%m-%d') AS DATE FROM sms_logs 
WHERE DATE >='2015-03-20' AND DATE <= '2015-04-30'
GROUP BY DATE_FORMAT(DATE,'%Y-%m-%d')

我想要显示数据为：

GINTO  ||   USD  ||   DATE
2222000 ||   200  || 2015-03-23
3366000 ||   300  || 2015-03-24
11000   ||   10   || 2015-03-25

但结果与该查询：

GINTO  ||   USD ||   DATE
2222000 || 284.65|| 2015-03-23
3366000 || 284.65|| 2015-03-24
11000   || 284.65|| 2015-03-25

在USD列中显示所有记录的摘要.为什么？

解决方法

您的计数调用在单独的查询中运行,没有group by子句,因此无论“主”查询的goruping如何,它们都应用于整个表.解决这个问题的一种方法是摆脱子查询并将它们带入“主”查询：

SELECT   SUM(price) AS GINTO,SUM(CASE price WHEN '200000' THEN 9.99
                        WHEN '60000'  THEN 3.99
                        WHEN '24000'  THEN 1.99
                        WHEN '11000'  THEN 0.99
              END) AS usd,DATE_FORMAT(date,'%Y-%m-%d') AS date 
FROM     sms_logs 
WHERE    date >='2015-03-20' AND date <= '2015-04-30'
GROUP BY DATE_FORMAT(date,'%Y-%m-%d')

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!