22,210
社区成员
发帖
与我相关
我的任务
分享
--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,2
CREATE 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
*/
--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 行受影响)
*/
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
--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.AID=B.BID GROUP BY AID,QTY,CNT,B.NAME ORDER BY AID,B.NAME,QTY DESC
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 B
SELECT 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.QTY
DECLARE @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