sQL数据库关于3张表的查询语句

amazeyeli

[code=sql]USE tempdb
IF OBJECT_ID('TEST1') IS NOT NULL
  DROP TABLE TEST1;  
GO
CREATE TABLE TEST1
(
  name nvarchar(10),
  pal  nvarchar(10)
);
GO
IF OBJECT_ID('TEST2') IS NOT NULL
  DROP TABLE TEST2;
GO
CREATE TABLE TEST2
(
  date date,
  name nvarchar(10),
  [in] int
);
GO
IF OBJECT_ID('TEST3') IS NOT NULL
  DROP TABLE TEST3;
GO
CREATE TABLE TEST3
(
  date date,
  name nvarchar(10),
  [out]int
);
GO
INSERT INTO TEST1 VALUES
('p1','大米'),
('p2','小麦'),
('p3','高粱'),
('p4','玉米'),
('p5','小米');
GO
INSERT INTO TEST2 VALUES
('2012-9-1','p1',10),
('2012-9-2','p2',30),
('2012-9-3','p3',50),
('2012-9-4','p4',20),
('2012-9-5','p5',15);
GO
INSERT INTO TEST3 VALUES
('2012-9-1','p1',25),
('2012-9-2','p4',35),
('2012-9-3','p1',50),
('2012-9-4','p4',40),
('2012-9-5','p2',10);
GO
SELECT A.name,A.pal,
       SUM(ISNULL(B.[in],0))  AS [in],
       SUM(ISNULL(C.[out],0)) AS [out]
FROM TEST1 AS A
LEFT JOIN TEST2 AS B
ON A.name = B.name
LEFT JOIN TEST3 AS C
ON A.name = C.name
GROUP BY A.name,A.pal
/*
name       pal        in          out
---------- ---------- ----------- -----------
p1         大米         20          75
p3         高粱         50          0
p2         小麦         30          10
p5         小米         15          0
p4         玉米         40          75

(5 行受影响)
*/ [/code]

hw2166

amazeyeli 发表于 2012-9-24 18:36

老师，你好，我用你教我的语句查询出来的结果怎么会出现如下的错误，请帮忙看一下错误在哪里，谢谢

1. 
   
2. select * from c_dress_pal where c_style_id='24f03' and c_cloth_id='m12311'
   
3. 
   
4. select c_style_id,c_cloth_id,sum(c_size1) as b1,sum(c_size2) as b2,sum(c_size3) as b3 from c_dress_stock
   
5.        where c_style_id='24f03' and c_cloth_id='m12311' GROUP BY c_style_id,c_cloth_id
   
6. 
   
7. select c_style_id,c_cloth_id,c_size1,c_size2,c_size3 from c_dress_stock
   
8.        where c_style_id='24f03' and c_cloth_id='m12311'
   
9. 
   
10. select c_style_id,c_cloth_id,sum(c_size1) as c1,sum(c_size2) as c2,sum(c_size3) as c3 from c_dress_sale_d
    
11.        where c_style_id='24f03' and c_cloth_id='m12311' GROUP BY c_style_id,c_cloth_id
    
12. 
    
13. select c_style_id,c_cloth_id,c_size1,c_size2,c_size3 from c_dress_sale_d
    
14.        where c_style_id='24f03' and c_cloth_id='m12311'
    
15. 
    
16. SELECT A.c_style_year,A.c_style_jijie,A.c_style_id,A.c_cloth_id,
    
17.        SUM(ISNULL(B.[c_size1],0))  AS [b1],SUM(ISNULL(B.[c_size2],0))  AS [b2], SUM(ISNULL(B.[c_size3],0))  AS [b3],
    
18.        SUM(ISNULL(C.[c_size1],0)) AS [c1],SUM(ISNULL(C.[c_size2],0)) AS [c2], SUM(ISNULL(C.[c_size3],0)) AS [c3]
    
19. FROM c_dress_pal AS A
    
20. LEFT JOIN c_dress_stock AS B
    
21. ON A.c_style_id = B.c_style_id and A.c_cloth_id = B.c_cloth_id
    
22. LEFT JOIN c_dress_sale_d AS C
    
23. ON A.c_style_id = C.c_style_id and A.c_cloth_id = C.c_cloth_id where A.c_style_id='24f03' and A.c_cloth_id='m12311'
    
24. GROUP BY A.c_style_year,A.c_style_jijie,A.c_style_id,A.c_cloth_id
    

复制代码

hw2166

amazeyeli 发表于 2012-9-24 18:36

老师，你好，我用你教我的语句查询出来的结果怎么会出现如下的错误，请帮忙看一下错误在哪里，谢谢

amazeyeli

产生了笛卡尔积,是我搞错了,大米的P1 in 只有10,join到3表时有两条,求和便成了20

应该先聚合,再关联

[code=sql]declare @i varchar(20) = '24f03',
        @j varchar(20) = 'm12311'
;with
a as
(
  select c_style_year,c_style_jijie,c_style_id,c_cloth_id
  from c_dress_pal
  where c_style_id=@i and c_cloth_id=@j
),
b as
(
  select c_style_id,c_cloth_id,
         sum(c_size1) as b1,sum(c_size2) as b2,sum(c_size3) as b3
  from c_dress_stock
  where c_style_id=@i and c_cloth_id=@j
  group by c_style_id,c_cloth_id
),
c as
(
  select c_style_id,c_cloth_id,
         sum(c_size1) as c1,sum(c_size2) as c2,sum(c_size3) as c3
  from c_dress_sale_d
  where c_style_id=@i and c_cloth_id=@j
  group by c_style_id,c_cloth_id
)
select a.*,b.b1,b.b2,b.b3,c.c1,c.c2,c.c3
from a
join b
on a.c_style_id = b.c_style_id and a.c_cloth_id = b.c_cloth_id
join c
on a.c_style_id = c.c_style_id and a.c_cloth_id = c.c_cloth_id [/code]

hw2166

amazeyeli 发表于 2012-9-25 12:45
产生了笛卡尔积,是我搞错了,大米的P1 in 只有10,join到3表时有两条,求和便成了20

应该先聚合,再关联

谢谢，又学会一样东西

hw2166

amazeyeli 发表于 2012-9-25 12:45
产生了笛卡尔积,是我搞错了,大米的P1 in 只有10,join到3表时有两条,求和便成了20

应该先聚合,再关联

老师，你好，再请教一下，上边这段代码查询出来的结果必须是 a , b ,c三张表同时存在的数据才能显示出来，（a表中包括所有货品参数，b，c表中只有部分a表的数据，即a包含b,c）
如：

amazeyeli

你看下联机丛书
join
left join
right join
的区别

amazeyeli

按Shift+F1就会出现光标所在处的帮助

hw2166

amazeyeli 发表于 2012-9-25 15:16
按Shift+F1就会出现光标所在处的帮助

我弄好了，谢谢老师