查询的sql语句增加一个查询条件
SELECT top 50 username,num1,num2,CAST(num2 AS INT)-CAST(num1 AS INT),CAST(num1 AS DECIMAL(10,2)),
(CAST(num2 AS INT)-CAST(num1 AS INT))/CAST(num1 AS DECIMAL(10,2)) as b FROM (
select c.username,
case WHEN num1 IS NULL THEN 0 ELSE num1 END AS num1,
case WHEN num2 IS NULL THEN 0 ELSE num2 END AS num2
FROM (SELECT username, count(1) AS num1 FROM tblorders a join tbluser b ON a.ProviderID= userid WHERE OrderStatus>0
and ordertime between '2012-01-01 00:00:00' and '2012-02-01 23:59:59' group by b.username) AS c
LEFT JOIN
(SELECT username,count(1) AS num2 FROM tblorders a join tbluser b ON a.ProviderID= b.userid WHERE OrderStatus>0
and ordertime between '2012-02-01 00:00:00' and '2012-02-05 23:59:59' group by b.username) AS d
ON c.username=d.username
) e order by b desc;
这是tblorders表 和tbluser 表联合查询
tblorders 表有个字段城市 Scity 比如 where Scity ='值'
怎么把这个条件加上啊 ，谢谢 了
[解决办法]
Try this
SELECT top 50 username,num1,num2,CAST(num2 AS INT)-CAST(num1 AS INT),CAST(num1 AS DECIMAL(10,2)),
(CAST(num2 AS INT)-CAST(num1 AS INT))/CAST(num1 AS DECIMAL(10,2)) as b FROM (
select c.username,
case WHEN num1 IS NULL THEN 0 ELSE num1 END AS num1,
case WHEN num2 IS NULL THEN 0 ELSE num2 END AS num2
FROM (SELECT username, count(1) AS num1 FROM tblorders a join tbluser b ON a.ProviderID= userid WHERE OrderStatus>0 and a.Scity ='值'
and ordertime between '2012-01-01 00:00:00' and '2012-02-01 23:59:59' group by b.username) AS c
LEFT JOIN
(SELECT username,count(1) AS num2 FROM tblorders a join tbluser b ON a.ProviderID= b.userid WHERE OrderStatus>0
and ordertime between '2012-02-01 00:00:00' and '2012-02-05 23:59:59' group by b.username) AS d
ON c.username=d.username
) e order by b desc;
