SELECT COUNT(*) AS dayCount,
(SELECT isnull(SUM(z16.am39), 0)
FROM p5, z16
WHERE z16.a37 = p5.a12 AND datediff(day, getdate(), p5.A14) = - 1 AND
z16.a28 = '住宅') AS dayArea,
(SELECT isnull(SUM(p12.a5 * 10000000 + p12.a6 * 1000000 + p12.a7 * 100000 + p12.a8
* 10000 + p12.a9 * 1000 + p12.a10 * 100 + p12.a11 * 10 + p12.a12) / 10000,
0)
FROM p12, p5, z16
WHERE p5.a10 = p12.id AND p5.a12 = z16.a37 AND datediff(day, getdate(), p5.A14)
= - 1 AND z16.a28 = '住宅') AS dayMoney
FROM P5 INNER JOIN
z16 ON P5.A12 = z16.a37
WHERE (DATEDIFF(day, GETDATE(), P5.A14) = - 1) AND (z16.a28 = '住宅')这个语句当DATEDIFF(day, GETDATE(), P5.A14) = - 1是查询昨天的=0是今天的现在我想查询7天的怎么写?被搞糊涂了
(SELECT isnull(SUM(z16.am39), 0)
FROM p5, z16
WHERE z16.a37 = p5.a12 AND datediff(day, getdate(), p5.A14) = - 1 AND
z16.a28 = '住宅') AS dayArea,
(SELECT isnull(SUM(p12.a5 * 10000000 + p12.a6 * 1000000 + p12.a7 * 100000 + p12.a8
* 10000 + p12.a9 * 1000 + p12.a10 * 100 + p12.a11 * 10 + p12.a12) / 10000,
0)
FROM p12, p5, z16
WHERE p5.a10 = p12.id AND p5.a12 = z16.a37 AND datediff(day, getdate(), p5.A14)
= - 1 AND z16.a28 = '住宅') AS dayMoney
FROM P5 INNER JOIN
z16 ON P5.A12 = z16.a37
WHERE (DATEDIFF(day, GETDATE(), P5.A14) = - 1) AND (z16.a28 = '住宅')这个语句当DATEDIFF(day, GETDATE(), P5.A14) = - 1是查询昨天的=0是今天的现在我想查询7天的怎么写?被搞糊涂了
0>=DATEDIFF(day, GETDATE(), P5.A14)<=-7是前7天
要7天的数据,7条===============================================具体的条数要看你数据库的数据阿
给出的都是正确的
请查看数据库的数据最好贴出数据来,大家可以测试
我只是根据一个表 p5的a14(一个日期) 得到与两个表关联的数据的和
其中p5的a12 和z16的a37来关联
p5的10 和 p12的id 字段关联
a14 2006-12-7 (日期) 日期型
a12 10(关联z16的a37) int
a10 9 (关联p12) int
.
.
.
p12 表
id 9 (标识)
a12 10 int(关联)
a5 6 int
a6 1 int
a7 3 int
a9 2 int
a10 5 intz16 表
a37 10(...) int
a28 住宅 文本类型
其中 p5中是一条主的合同,p12里面是关于这个合同的金额,z16中有这个合同的面积
我想得到今天以前7天每天的总金额和总面积
a14 2006-12-7 (日期) 日期型
a12 10(关联z16的a37) int
a10 9 (关联p12) int
.
.
.
p12 表
id 9 (标识)
a12 10 int
a5 6 int
a6 1 int
a7 3 int
a9 2 int
a10 5 intz16 表
a37 10(...) int
a28 住宅 文本类型
其中 p5中是一条主的合同,p12里面是关于这个合同的金额,z16中有这个合同的面积
我想得到今天以前7天每天的总金额和总面积
select a.a14, isnull(SUM(z16.am39), 0) as dayArea,
isnull(SUM(c.a5 * 10000000 + c.a6 * 1000000 + c.a7 * 100000 + c.a8 * 10000 + c.a9 * 1000 + c.a10 * 100 + c.a11 * 10 + c.a12) / 10000,0) as dayMoney
from p5 a inner join z16 b on b.a37 = a.a12
inner join p12 c on a.a10 = c.id
where b.a28 = '住宅' and DATEDIFF(day, a.A14, GETDATE()) <= 7