我有两个表t1、t2t1:(字段1 和 字段2 里的值是t2的id2的值)id1 字段1 字段2 日期
----------------------------
1 1 1 d1
2 2 1 d1
3 NULL 2 d2
t2:id2 价格
------------------
1 10
2 13.5
现在我要对t1表进行按日期对 字段1 和 字段2 的价格求和,最后显示出来的是26.5 20 d1
NULL 13.5 d2请问用一句sql语句怎么写?鞠躬谢谢
----------------------------
1 1 1 d1
2 2 1 d1
3 NULL 2 d2
t2:id2 价格
------------------
1 10
2 13.5
现在我要对t1表进行按日期对 字段1 和 字段2 的价格求和,最后显示出来的是26.5 20 d1
NULL 13.5 d2请问用一句sql语句怎么写?鞠躬谢谢
from (
select id1,
(select 价格 from t2 where id2=字段1) as p1,
(select 价格 from t2 where id2=字段2) as p2,
日期
from t1
) t
group by 日期
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([id1] int,[字段1] int,[字段2] int,[日期] varchar(2))
insert [t1]
select 1,1,1,'d1' union all
select 2,2,1,'d1' union all
select 3,null,2,'d2'
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([id2] int,[价格] numeric(3,1))
insert [t2]
select 1,10 union all
select 2,13.5
---查询---
select sum(t2.价格) as 字段1,sum(t3.价格) as 字段2,日期
from t1
left join t2 on t1.字段1=t2.id2
left join t2 t3 on t1.字段2=t3.id2
group by 日期---结果---
字段1 字段2 日期
---------------------------------------- ---------------------------------------- ----
23.5 20.0 d1
NULL 13.5 d2(所影响的行数为 2 行)警告: 聚合或其它 SET 操作消除了空值。
mysql> select * from t1;
+------+-------+-------+------+
| id1 | 字段1 | 字段2 | 日期 |
+------+-------+-------+------+
| 1 | 1 | 1 | d1 |
| 2 | 2 | 1 | d1 |
| 3 | NULL | 2 | d2 |
+------+-------+-------+------+
3 rows in set (0.00 sec)mysql> select * from t2;
+------+-------+
| id2 | 价格 |
+------+-------+
| 1 | 10.00 |
| 2 | 13.50 |
+------+-------+
2 rows in set (0.00 sec)mysql> select sum(p1),sum(p2),日期
-> from (
-> select id1,
-> (select 价格 from t2 where id2=字段1) as p1,
-> (select 价格 from t2 where id2=字段2) as p2,
-> 日期
-> from t1
-> ) t
-> group by 日期;
+---------+---------+------+
| sum(p1) | sum(p2) | 日期 |
+---------+---------+------+
| 23.50 | 20.00 | d1 |
| NULL | 13.50 | d2 |
+---------+---------+------+
2 rows in set (0.00 sec)mysql>
楼主能说明一下你的 26.5 是怎么来的?