CREATE TABLE A(Aid INT, Cnt VARCHAR(10), qty INT, Bid INT) INSERT A SELECT 1 , '1-12' , 11 , 1 UNION ALL SELECT 1 , '13-15' , 11 , 1 UNION ALL SELECT 2 , '18' , 12 , 1 UNION ALL SELECT 2 , '19-20' , 10 , 2 UNION ALL SELECT 3 , '22-24' , 12 , 2 UNION ALL SELECT 3 , '25-30' , 10 , 2 CREATE TABLE B (Bid INT, name VARCHAR(10)) INSERT B SELECT 1 , 'A' UNION SELECT 2 , 'B' UNION SELECT 3 , 'C' --DROP TABLE B--SELECT * FROM A --SELECT * FROM BSELECT DISTINCT A.AID,CNT INTO #TB FROM A , (SELECT AID,QTY FROM A WHERE NOT EXISTS (SELECT 1 FROM A T WHERE A.AID=T.AID AND A.QTY<>T.QTY ))AS T1 WHERE A.AID=T1.AID AND A.QTY=T1.QTYDECLARE @STR VARCHAR(8000) SELECT @STR=ISNULL(@STR+',','')+CNT FROM #TB SELECT @STR UPDATE A SET CNT=@STR WHERE AID IN (SELECT AID FROM #TB) SELECT AID,B.NAME,CNT,SUM(QTY)QTY FROM A,B WHERE A.AID=B.BID GROUP BY AID,QTY,CNT,B.NAME ORDER BY AID,B.NAME AID NAME CNT QTY ----------- ---------- ---------- ----------- 1 A 1-12,13-15 22 2 B 19-20 10 2 B 18 12 3 C 25-30 10 3 C 22-24 12(所影响的行数为 5 行)
稍微改下排序! SELECT AID,B.NAME,CNT,SUM(QTY)QTY FROM A,B WHERE A.AID=B.BID GROUP BY AID,QTY,CNT,B.NAME ORDER BY AID,B.NAME,QTY DESCAID NAME CNT QTY ----------- ---------- ---------- ----------- 1 A 1-12,13-15 22 2 B 18 12 2 B 19-20 10 3 C 22-24 12 3 C 25-30 10(所影响的行数为 5 行)
你的结果是怎么弄的呀??A.AID=B.BID??还是A.BID=B.BID??
--try: ---------------------------------------------------------- CREATE FUNCTION getdata_uf ( @var varchar(500) ) RETURNS INT AS BEGIN declare @m int,@y varchar(500),@i int,@j int SET @m=0; SET @i=0; SET @j=0;if(charindex('-',@var)=0) begin set @M=1; RETURN @M end ELSE BEGIN SET @var=@var+',' while(charindex(',',@var)>0) begin SET @y=substring(@var,1,charindex(',',@var)) select @i=cast(substring(@y,1,charindex('-',@y)-1)as int) select @j=cast(substring(@y,charindex('-',@y)+1,len(@y)-charindex('-',@y)-1) as int) SET @m=@m+@j-@i+1 SET @var=substring(@var,charindex(',',@var)+1,len(@var)) end END ----------------------------------------------------------- RETURN @M END-------------------- DECLARE @M TABLE(AID INT,NAME VARCHAR(10),CNT VARCHAR(20),QTY INT,TOTALQTY INT) INSERT INTO @M SELECT 1,'A','1-12,13-15',11,22 UNION ALL SELECT 2,'A','18',12,12 UNION ALL SELECT 2,'B','19-20',10,10 UNION ALL SELECT 3,'C','22-24',12,12 UNION ALL SELECT 3,'C','25-30',10,10 SELECT *,DBO.getdata_uf(CNT)CNTT FROM @M /* AID NAME CNT QTY TOTALQTY CNTT ----------- ---------- -------------------- ----------- ----------- ----------- 1 A 1-12,13-15 11 22 15 2 A 18 12 12 1 2 B 19-20 10 10 2 3 C 22-24 12 12 3 3 C 25-30 10 10 6(5 行受影响) */
SELECT AID,B.NAME,CNT,SUM(QTY)QTY FROM A,B WHERE A.BID=B.BID GROUP BY AID,QTY,CNT,B.NAME ORDER BY AID,B.NAME,QTY DESC 把条件改下!把AID改成BID
--sql2005的解法 if object_id('[ta]') is not null drop table [ta] go create table [ta]([Aid] int,[Cnt] varchar(5),[qty] int,[Bid] int) insert [ta] select 1,'1-12',11,1 union all select 1,'13-15',11,1 union all select 2,'18',12,1 union all select 2,'19-20',10,2 union all select 3,'22-24',12,2 union all select 3,'25-30',10,2 go if object_id('[tb]') is not null drop table [tb] go create table [tb]([Bid] int,[name] varchar(1)) insert [tb] select 1,'A' union all select 2,'B' union all select 3,'C' go --select * from [ta] --select * from [tb]select a.Aid,b.name ,Cnt=stuff((select ','+Cnt from ta where Aid=a.Aid and qty=a.qty and Bid=b.Bid for xml path('')),1,1,'') ,a.qty,totalqty=sum(a.qty) from ta a join tb b on a.Bid=b.Bid group by a.Aid,a.qty,b.Bid,b.name order by a.Aid,b.name,Cnt /* Aid name Cnt qty totalqty ----------- ---- --------------- ----------- ----------- 1 A 1-12,13-15 11 22 2 A 18 12 12 2 B 19-20 10 10 3 B 22-24 12 12 3 B 25-30 10 10(5 行受影响) */
--SQL2000方法1 CREATE TABLE #A([Aid] INT,[Cnt.] VARCHAR(20),[qty] INT,[Bid] INT) INSERT INTO #A SELECT 1,'1-12',11,1 UNION ALL SELECT 1,'13-15',11,1 UNION ALL SELECT 2,'18',12,1 UNION ALL SELECT 2,'19-20',10,2 UNION ALL SELECT 3,'22-24',12,2 UNION ALL SELECT 3,'25-30',10,2CREATE TABLE #B([Bid] int,[name] varchar(20)) INSERT INTO #B select 1,'A' union all select 2,'B' union all select 3,'C'select a.Aid,[name] ,[Cnt.]=cast(min([Cnt.]) as varchar) +case when count(1)=1 then '' else +','+cast(max([Cnt.]) as varchar) end ,qty,sum(qty) totalqty from #A a join #B b on a.Aid=b.Bid group by a.Aid,[NAME],qty ORDER BY 3 /* Aid name Cnt. qty totalqty 1 A 1-12,13-15 11 22 2 B 18 12 12 2 B 19-20 10 10 3 C 22-24 12 12 3 C 25-30 10 10 */
INSERT A
SELECT 1 , '1-12' , 11 , 1 UNION ALL
SELECT 1 , '13-15' , 11 , 1 UNION ALL
SELECT 2 , '18' , 12 , 1 UNION ALL
SELECT 2 , '19-20' , 10 , 2 UNION ALL
SELECT 3 , '22-24' , 12 , 2 UNION ALL
SELECT 3 , '25-30' , 10 , 2 CREATE TABLE B (Bid INT, name VARCHAR(10))
INSERT B
SELECT 1 , 'A' UNION
SELECT 2 , 'B' UNION
SELECT 3 , 'C' --DROP TABLE B--SELECT * FROM A
--SELECT * FROM BSELECT DISTINCT A.AID,CNT INTO #TB FROM A ,
(SELECT AID,QTY FROM A WHERE NOT EXISTS (SELECT 1 FROM A T WHERE A.AID=T.AID AND A.QTY<>T.QTY ))AS T1
WHERE A.AID=T1.AID AND A.QTY=T1.QTYDECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR+',','')+CNT
FROM #TB
SELECT @STR
UPDATE A SET CNT=@STR WHERE AID IN (SELECT AID FROM #TB)
SELECT AID,B.NAME,CNT,SUM(QTY)QTY FROM A,B WHERE A.AID=B.BID GROUP BY AID,QTY,CNT,B.NAME ORDER BY AID,B.NAME
AID NAME CNT QTY
----------- ---------- ---------- -----------
1 A 1-12,13-15 22
2 B 19-20 10
2 B 18 12
3 C 25-30 10
3 C 22-24 12(所影响的行数为 5 行)
SELECT AID,B.NAME,CNT,SUM(QTY)QTY FROM A,B WHERE A.AID=B.BID GROUP BY AID,QTY,CNT,B.NAME ORDER BY AID,B.NAME,QTY DESCAID NAME CNT QTY
----------- ---------- ---------- -----------
1 A 1-12,13-15 22
2 B 18 12
2 B 19-20 10
3 C 22-24 12
3 C 25-30 10(所影响的行数为 5 行)
你的结果是怎么弄的呀??A.AID=B.BID??还是A.BID=B.BID??
--try:
----------------------------------------------------------
CREATE FUNCTION getdata_uf
(
@var varchar(500)
)
RETURNS INT
AS
BEGIN
declare @m int,@y varchar(500),@i int,@j int
SET @m=0;
SET @i=0;
SET @j=0;if(charindex('-',@var)=0)
begin
set @M=1;
RETURN @M
end
ELSE
BEGIN
SET @var=@var+','
while(charindex(',',@var)>0)
begin
SET @y=substring(@var,1,charindex(',',@var))
select @i=cast(substring(@y,1,charindex('-',@y)-1)as int)
select @j=cast(substring(@y,charindex('-',@y)+1,len(@y)-charindex('-',@y)-1) as int)
SET @m=@m+@j-@i+1
SET @var=substring(@var,charindex(',',@var)+1,len(@var))
end
END
-----------------------------------------------------------
RETURN @M
END--------------------
DECLARE @M TABLE(AID INT,NAME VARCHAR(10),CNT VARCHAR(20),QTY INT,TOTALQTY INT)
INSERT INTO @M
SELECT 1,'A','1-12,13-15',11,22 UNION ALL
SELECT 2,'A','18',12,12 UNION ALL
SELECT 2,'B','19-20',10,10 UNION ALL
SELECT 3,'C','22-24',12,12 UNION ALL
SELECT 3,'C','25-30',10,10 SELECT *,DBO.getdata_uf(CNT)CNTT FROM @M
/*
AID NAME CNT QTY TOTALQTY CNTT
----------- ---------- -------------------- ----------- ----------- -----------
1 A 1-12,13-15 11 22 15
2 A 18 12 12 1
2 B 19-20 10 10 2
3 C 22-24 12 12 3
3 C 25-30 10 10 6(5 行受影响)
*/
把条件改下!把AID改成BID
--sql2005的解法
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([Aid] int,[Cnt] varchar(5),[qty] int,[Bid] int)
insert [ta]
select 1,'1-12',11,1 union all
select 1,'13-15',11,1 union all
select 2,'18',12,1 union all
select 2,'19-20',10,2 union all
select 3,'22-24',12,2 union all
select 3,'25-30',10,2
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Bid] int,[name] varchar(1))
insert [tb]
select 1,'A' union all
select 2,'B' union all
select 3,'C'
go
--select * from [ta]
--select * from [tb]select a.Aid,b.name
,Cnt=stuff((select ','+Cnt from ta where Aid=a.Aid and qty=a.qty and Bid=b.Bid for xml path('')),1,1,'')
,a.qty,totalqty=sum(a.qty)
from ta a join tb b
on a.Bid=b.Bid
group by a.Aid,a.qty,b.Bid,b.name
order by a.Aid,b.name,Cnt
/*
Aid name Cnt qty totalqty
----------- ---- --------------- ----------- -----------
1 A 1-12,13-15 11 22
2 A 18 12 12
2 B 19-20 10 10
3 B 22-24 12 12
3 B 25-30 10 10(5 行受影响)
*/
CREATE TABLE #A([Aid] INT,[Cnt.] VARCHAR(20),[qty] INT,[Bid] INT)
INSERT INTO #A
SELECT 1,'1-12',11,1 UNION ALL
SELECT 1,'13-15',11,1 UNION ALL
SELECT 2,'18',12,1 UNION ALL
SELECT 2,'19-20',10,2 UNION ALL
SELECT 3,'22-24',12,2 UNION ALL
SELECT 3,'25-30',10,2CREATE TABLE #B([Bid] int,[name] varchar(20))
INSERT INTO #B
select 1,'A' union all
select 2,'B' union all
select 3,'C'select a.Aid,[name]
,[Cnt.]=cast(min([Cnt.]) as varchar)
+case when count(1)=1 then '' else +','+cast(max([Cnt.]) as varchar) end ,qty,sum(qty) totalqty
from #A a
join #B b on a.Aid=b.Bid
group by a.Aid,[NAME],qty
ORDER BY 3
/*
Aid name Cnt. qty totalqty
1 A 1-12,13-15 11 22
2 B 18 12 12
2 B 19-20 10 10
3 C 22-24 12 12
3 C 25-30 10 10
*/