SQL如何实现以下数据统计查询

Question

一个用户订货明细表如下：
id userid product_name qty 
1 chenhh P1 10
2 chenhh P2 20
3 test P3 30
4 test P4 40
5 test P1 50我想按照这个表的product_name的最大并集并按照product_name名称排序进行数据统计，
通过SQL查询此表得到如下结果：
userid product_id product_name qty 
chenhh 1 P1 10
chenhh 2 P2 20
chenhh 2 P3 0
chenhh 2 P4 0
test 2 P1 50
test 2 P2 0
test 3 P3 30
test 4 P4 40请指教~谢谢！

Answer 1

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-07-20 14:07:06
-------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,userid VARCHAR(6),product_name VARCHAR(2),qty INT)
INSERT INTO @T
SELECT 1,'chenhh','P1',10 UNION ALL
SELECT 2,'chenhh','P2',20 UNION ALL
SELECT 3,'test','P3',30 UNION ALL
SELECT 4,'test','P4',40 UNION ALL
SELECT 5,'test','P1',50--SQL查询如下:
SELECT A.userid,A.product_name,ISNULL(B.qty,0) AS qty
FROM (SELECT * FROM (SELECT DISTINCT userid FROM @T) AS A
             CROSS JOIN (SELECT DISTINCT product_name FROM @T) AS B) AS A
    LEFT JOIN @T AS B
ON A.userid = B.userid AND A.product_name = B.product_name
ORDER BY A.userid,A.product_name/*
userid product_name qty
------ ------------ -----------
chenhh P1           10
chenhh P2           20
chenhh P3           0
chenhh P4           0
test   P1           50
test   P2           0
test   P3           30
test   P4           40(8 行受影响)
*/

Answer 2

select  
    a.userid,b.product_name,isnull(sum(c.qty),0) qty
from
    (select distinct userid       from 用户订货明细表) a
cross join
    (select distinct product_name from 用户订货明细表) b
left join
    用户订货明细表 c
on
    a.userid=c.userid and b.product_name=c.product_name
group by
    a.userid,b.product_name
order by
    a.userid,b.product_name

Answer 3

declare @t table(id int,userid varchar(10),product_name varchar(10),qty int)
insert into @t select 1,'chenhh','P1',10 
insert into @t select 2,'chenhh','P2',20 
insert into @t select 3,'test  ','P3',30 
insert into @t select 4,'test  ','P4',40 
insert into @t select 5,'test  ','P1',50 
select  
    a.userid,b.product_name,isnull(sum(c.qty),0) qty
from
    (select distinct userid       from @t) a
cross join
    (select distinct product_name from @t) b
left join
    @t c
on
    a.userid=c.userid and b.product_name=c.product_name
group by
    a.userid,b.product_name
order by
    a.userid,b.product_name/*
userid     product_name qty         
---------- ------------ ----------- 
chenhh     P1           10
chenhh     P2           20
chenhh     P3           0
chenhh     P4           0
test       P1           50
test       P2           0
test       P3           30
test       P4           40
*/

Answer 4

declare @t table(id int,userid varchar(10),product_name varchar(10),qty int)
insert into @t select 1,'chenhh','P1',10 
insert into @t select 2,'chenhh','P2',20 
insert into @t select 3,'test  ','P3',30 
insert into @t select 4,'test  ','P4',40 
insert into @t select 5,'test  ','P1',50 
select  
    a.userid,b.product_name,isnull(sum(c.qty),0) qty
from
    (select distinct userid       from @t) a
cross join
    (select distinct product_name from @t) b
left join
    @t c
on
    a.userid=c.userid and b.product_name=c.product_name
group by
    a.userid,b.product_name
order by
    a.userid,b.product_name/*
userid     product_name qty         
---------- ------------ ----------- 
chenhh     P1           10
chenhh     P2           20
chenhh     P3           0
chenhh     P4           0
test       P1           50
test       P2           0
test       P3           30
test       P4           40
*/

Answer 5

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[userid] varchar(6),[product_name] varchar(2),[qty] int)
insert [tb]
select 1,'chenhh','P1',10 union all
select 2,'chenhh','P2',20 union all
select 3,'test','P3',30 union all
select 4,'test','P4',40 union all
select 5,'test','P1',50
 
---查询---
select 
  a.userid,
  isnull(b.id,(select max(id) from tb where userid=a.userid)) as product_id,
  a.product_name,
  isnull(b.qty,0) as qty
from
(
  select 
    *
  from
    (select distinct product_name from tb) a,
    (select distinct userid from tb) b
) a
left join
  tb b
on
  a.userid=b.userid and a.product_name=b.product_name
order by 
  a.userid,
  a.product_name  
---结果---
userid product_id  product_name qty         
------ ----------- ------------ ----------- 
chenhh 1           P1           10
chenhh 2           P2           20
chenhh 2           P3           0
chenhh 2           P4           0
test   5           P1           50
test   5           P2           0
test   3           P3           30
test   4           P4           40（所影响的行数为 8 行）
没搞懂楼主的product_id是怎么取的