create table TABLE_A(A VARCHAR(20),B VARCHAR(20),C VARCHAR(20),D VARCHAR(20)) create table TABLE_B(IDD VARCHAR(20),A VARCHAR(20),Price INT,FarPrice INT,ParPrice INT)insert into TABLE_A select '1','2','3','4'insert into TABLE_B select '1','1',200,30,200 UNION ALL SELECT '2','1',300,20,300SELECT m.A,COUNT(m.A),SUM(n.Price),SUM(n.FarPrice),SUM(n.ParPrice) FROM TABLE_A m,TABLE_B n WHERE m.A=n.A GROUP BY m.A
SELECT t.表1A,t.Count(表1A),h.Sum(表2Price),h.Sum(表2FarPrice),h.Sum(表2ParPrice) FROM (SELECT 表1A,count(*) AS 'Count(表1A)' from 表1 group by 表1A) t, (SELECT 表1A,Sum(表2Price) AS 'Sum(表2Price)',Sum(表2FarPrice) AS 'Sum(表2FarPrice)',Sum(表2ParPrice) AS 'Sum(表2ParPrice)' FROM 表2 GROUP BY 表1A) h WHERE t.表1A = h.表1A
Count(表1A) 4 怎麼來,規則是?Count(表1A) 4 是表1的所以数据条数
DECLARE @T1 TABLE(A INT, B INT, C INT, D INT) INSERT @T1 SELECT 1, 2, 3, 4 INSERT @T1 SELECT 2 , 22, 33, 44 INSERT @T1 SELECT 3 , 33, 33, 33 INSERT @T1 SELECT 9, 99, 99, 99 DECLARE @T2 TABLE(ID INT, A INT, Price INT, FarPrice INT, ParPrice INT) INSERT @T2 SELECT 1 , 1, 200, 30, 200 INSERT @T2 SELECT 2, 1, 300, 20 , 300 SELECT (SELECT COUNT(*) FROM @T1)[Count],SUM(B.PRICE)PRICE,SUM(B.FARPRICE)FARPRICE,SUM(B.PARPRICE)PARPRICE FROM @T1 A INNER JOIN @T2 B ON A.A=B.A GROUP BY A.A /*Count PRICE FARPRICE PARPRICE ----------- ----------- ----------- ----------- 4 500 50 500*/
select (select count(1) from 表1) as 表1A, sum(表2Price),Sum(表2FarPrice),Sum(表2ParPrice) from 表2 group by 表1A
1.感觉根本没涉及到表1,不知楼上各位联合查询什么意义 2.select count(表1A),sum(表2Price),Sum(表2FarPrice),Sum(表2ParPrice) from 表2 group by 表1A
select 表1A,sum(表2Price),Sum(表2FarPrice),Sum(表2ParPrice) from 表2 group by 表1A
DECLARE @T1 TABLE(A INT, B INT, C INT, D INT) INSERT @T1 SELECT 1, 2, 3, 4 INSERT @T1 SELECT 2 , 22, 33, 44 INSERT @T1 SELECT 3 , 33, 33, 33 INSERT @T1 SELECT 9, 99, 99, 99 DECLARE @T2 TABLE(ID INT, A INT, Price INT, FarPrice INT, ParPrice INT) INSERT @T2 SELECT 1 , 1, 200, 30, 200 INSERT @T2 SELECT 2, 1, 300, 20 , 300 INSERT @T2 SELECT 3, 2, 300, 20 , 300 SELECT (SELECT COUNT(*) FROM @T1) as [Count(表1A)],SUM(B.PRICE) as [Sum(表2Price)],SUM(B.FARPRICE) as [Sum(表2FarPrice)],SUM(B.PARPRICE) as [Sum(表2ParPrice)] FROM @T1 A INNER JOIN @T2 B ON A.A=B.A GROUP BY A.A
DECLARE @T1 TABLE(A INT, B INT, C INT, D INT) INSERT @T1 SELECT 1, 2, 3, 4 INSERT @T1 SELECT 2 , 22, 33, 44 INSERT @T1 SELECT 3 , 33, 33, 33 INSERT @T1 SELECT 9, 99, 99, 99 DECLARE @T2 TABLE(ID INT, A INT, Price INT, FarPrice INT, ParPrice INT) INSERT @T2 SELECT 1 , 1, 200, 30, 200 INSERT @T2 SELECT 2, 1, 300, 20 , 300 SELECT (SELECT COUNT(*) FROM @T1)[Count],SUM(B.PRICE)PRICE,SUM(B.FARPRICE)FARPRICE,SUM(B.PARPRICE)PARPRICE FROM @T1 A INNER JOIN @T2 B ON A.A=B.A GROUP BY A.A /*Count PRICE FARPRICE PARPRICE ----------- ----------- ----------- ----------- 4 500 50 500*/ 这个应该是楼主需要的结构吧,不过需求有点怪怪的,2表压根没有关系。
declare @table1Count int set @tablecount = select count(1) from 表1 group by 表1Aselect @tableCount,SUM(B.PRICE) ,SUM(B.FARPRICE) ,SUM(B.PARPRICE) FROM 表2 group by 表1A
select count(1),sum(b.a1),Sum(b.a2),Sum(b.a3) from 表1 a left join (select 表1A, sum(表2Price) as a1,Sum(表2FarPrice) as a2,Sum(表2ParPrice) as a3 from 表2 group by 表1A) b on a.表1A=b.表1A
--SQL SERVER 2005 SELECT tmp2.A, Price = SUM(tmp2.Price), FarPrice = SUM(tmp2.FarPrice), ParPrice = SUM(tmp2.ParPrice) FROM (SELECT Orders FROM (SELECT A,B,C,D FROM #1) p UNPIVOT (Orders FOR word IN (A,B,C,D) ) unpvt ) tmp1 INNER JOIN #2 tmp2 ON tmp1.Orders = tmp2.A GROUP BY tmp2.A
原来CSDN人才也不多啊 select count(distinct t1.*),Sum(t2.Price),Sum(t2.FarPrice),Sum(t2.ParPrice) from 表1 t1 inner join 表2 t2 on t1.A=t2.A 这里表1里的A列,应当为唯一值
select count(表1A),sum(表2Price),Sum(表2FarPrice),Sum(表2ParPrice) from 表2,表1 where 表1.表1A= 表2.表1A
from 表2
group by 表1A
from 表2
group by 表1A
INSERT @T1 SELECT 1, 2, 3, 4
DECLARE @T2 TABLE(ID INT, A INT, Price INT, FarPrice INT, ParPrice INT)
INSERT @T2 SELECT 1 , 1, 200, 30, 200
INSERT @T2 SELECT 2, 1, 300, 20 , 300
SELECT A.A,SUM(B.PRICE)PRICE,SUM(B.FARPRICE)FARPRICE,SUM(B.PARPRICE)PARPRICE FROM @T1 A INNER JOIN @T2 B ON A.A=B.A GROUP BY A.A
/*A PRICE FARPRICE PARPRICE
----------- ----------- ----------- -----------
1 500 50 500
*/
insert into @表1 select 1,2,3,4
declare @表2 table (表2id int,表1A int,表2price int,表2farprice int,表2parprice int)
insert into @表2 select 1,1,200,30,200
union all select 2,1,300,20,300
select b.表1A,SUM(表2price),SUM(表2farprice),SUM(表2parprice) from
@表2 b join @表1 a on b.表1A=a.表1A
group by b.表1A
表1A
----------- ----------- ----------- -----------
1 500 50 500(1 行受影响)
create table TABLE_B(IDD VARCHAR(20),A VARCHAR(20),Price INT,FarPrice INT,ParPrice INT)insert into TABLE_A select '1','2','3','4'insert into TABLE_B
select '1','1',200,30,200
UNION ALL SELECT '2','1',300,20,300SELECT m.A,COUNT(m.A),SUM(n.Price),SUM(n.FarPrice),SUM(n.ParPrice)
FROM TABLE_A m,TABLE_B n
WHERE m.A=n.A
GROUP BY m.A
1 2 3 4
2 22 33 44
3 33 33 33
9 99 99 99表2结构
表2ID 表1A 表2Price 表2FarPrice 表2ParPrice
1 1 200 30 200
2 1 300 20 300
就这两张表
现在我想得到一个结果
Count(表1A) Sum(表2Price) Sum(表2FarPrice) Sum(表2ParPrice)
4 500 50 500
4
怎麼來,規則是?
SELECT t.表1A,t.Count(表1A),h.Sum(表2Price),h.Sum(表2FarPrice),h.Sum(表2ParPrice) FROM
(SELECT 表1A,count(*) AS 'Count(表1A)' from 表1 group by 表1A) t,
(SELECT 表1A,Sum(表2Price) AS 'Sum(表2Price)',Sum(表2FarPrice) AS 'Sum(表2FarPrice)',Sum(表2ParPrice) AS 'Sum(表2ParPrice)'
FROM 表2 GROUP BY 表1A) h
WHERE t.表1A = h.表1A
4
怎麼來,規則是?Count(表1A)
4
是表1的所以数据条数
INSERT @T1 SELECT 1, 2, 3, 4
INSERT @T1 SELECT 2 , 22, 33, 44
INSERT @T1 SELECT 3 , 33, 33, 33
INSERT @T1 SELECT 9, 99, 99, 99
DECLARE @T2 TABLE(ID INT, A INT, Price INT, FarPrice INT, ParPrice INT)
INSERT @T2 SELECT 1 , 1, 200, 30, 200
INSERT @T2 SELECT 2, 1, 300, 20 , 300
SELECT (SELECT COUNT(*) FROM @T1)[Count],SUM(B.PRICE)PRICE,SUM(B.FARPRICE)FARPRICE,SUM(B.PARPRICE)PARPRICE FROM @T1 A INNER JOIN @T2 B ON A.A=B.A GROUP BY A.A
/*Count PRICE FARPRICE PARPRICE
----------- ----------- ----------- -----------
4 500 50 500*/
select (select count(1) from 表1) as 表1A,
sum(表2Price),Sum(表2FarPrice),Sum(表2ParPrice)
from 表2
group by 表1A
2.select count(表1A),sum(表2Price),Sum(表2FarPrice),Sum(表2ParPrice)
from 表2
group by 表1A
from 表2
group by 表1A
DECLARE @T1 TABLE(A INT, B INT, C INT, D INT)
INSERT @T1 SELECT 1, 2, 3, 4
INSERT @T1 SELECT 2 , 22, 33, 44
INSERT @T1 SELECT 3 , 33, 33, 33
INSERT @T1 SELECT 9, 99, 99, 99
DECLARE @T2 TABLE(ID INT, A INT, Price INT, FarPrice INT, ParPrice INT)
INSERT @T2 SELECT 1 , 1, 200, 30, 200
INSERT @T2 SELECT 2, 1, 300, 20 , 300
INSERT @T2 SELECT 3, 2, 300, 20 , 300
SELECT (SELECT COUNT(*) FROM @T1) as [Count(表1A)],SUM(B.PRICE) as [Sum(表2Price)],SUM(B.FARPRICE) as [Sum(表2FarPrice)],SUM(B.PARPRICE) as [Sum(表2ParPrice)] FROM @T1 A INNER JOIN @T2 B ON A.A=B.A GROUP BY A.A
结果:
Count(表1A) Sum(表2Price) Sum(表2FarPrice) Sum(表2ParPrice)
----------- ------------ --------------- ---------------
4 500 50 500
4 300 20 300
DECLARE @T1 TABLE(A INT, B INT, C INT, D INT) INSERT @T1 SELECT 1, 2, 3, 4 INSERT @T1 SELECT 2 , 22, 33, 44 INSERT @T1 SELECT 3 , 33, 33, 33 INSERT @T1 SELECT 9, 99, 99, 99 DECLARE @T2 TABLE(ID INT, A INT, Price INT, FarPrice INT, ParPrice INT) INSERT @T2 SELECT 1 , 1, 200, 30, 200 INSERT @T2 SELECT 2, 1, 300, 20 , 300 SELECT (SELECT COUNT(*) FROM @T1)[Count],SUM(B.PRICE)PRICE,SUM(B.FARPRICE)FARPRICE,SUM(B.PARPRICE)PARPRICE FROM @T1 A INNER JOIN @T2 B ON A.A=B.A GROUP BY A.A /*Count PRICE FARPRICE PARPRICE ----------- ----------- ----------- ----------- 4 500 50 500*/ 这个应该是楼主需要的结构吧,不过需求有点怪怪的,2表压根没有关系。
set
@tablecount =
select count(1)
from 表1
group by 表1Aselect @tableCount,SUM(B.PRICE) ,SUM(B.FARPRICE) ,SUM(B.PARPRICE)
FROM 表2
group by 表1A
SELECT tmp2.A,
Price = SUM(tmp2.Price),
FarPrice = SUM(tmp2.FarPrice),
ParPrice = SUM(tmp2.ParPrice)
FROM
(SELECT Orders
FROM
(SELECT A,B,C,D
FROM #1) p
UNPIVOT
(Orders FOR word IN (A,B,C,D) ) unpvt ) tmp1
INNER JOIN #2 tmp2
ON tmp1.Orders = tmp2.A
GROUP BY tmp2.A
select count(distinct t1.*),Sum(t2.Price),Sum(t2.FarPrice),Sum(t2.ParPrice)
from 表1 t1
inner join 表2 t2 on t1.A=t2.A
这里表1里的A列,应当为唯一值
from 表2,表1 where 表1.表1A= 表2.表1A