表A:ID JOBID NO CHARGE BILL_NO
1 20 1 100.00 101
2 35 1 50.00 102
3 20 1 200.00 101
4 20 2 30.00 201
5 35 2 40.00 210
得到结果:
JOBID CHARGE BILL_ID
20 350.00 101/201
35 70.00 102/210
1 20 1 100.00 101
2 35 1 50.00 102
3 20 1 200.00 101
4 20 2 30.00 201
5 35 2 40.00 210
得到结果:
JOBID CHARGE BILL_ID
20 350.00 101/201
35 70.00 102/210
select jobid,sum(charge) as charge,stuff((select '/'+bill_no from tabA
where jobid=t.jobid for xml path('')),1,1,'') as bill_id from taba as t
group by jobid楼主用数据验证下。
use test
if object_id('taba','u') is not null
drop table taba
go
create table taba(id int,jobid int,[no] int,charge decimal(18,2),bill_no int)
go
insert into taba
select 1, 20, 1, 100.00, 101 union all
select 2, 35 , 1 , 50.00 , 102 union all
select 3, 20, 1, 200.00, 101 union all
select 4, 20, 2, 30.00, 201 union all
select 5, 35, 2, 40.00, 210
go
select jobid,sum(charge) as charge,stuff((select distinct '/'+convert(varchar,bill_no) from tabA
where jobid=t.jobid for xml path('')),1,1,'') as bill_id from taba as t
group by jobid上面的有点错误,
看不明白分组的依据是什么。。
依据是JOBID,因为费用分多次结算(NO),最后账单希望得到每次结算的总和和账单号
看不明白分组的依据是什么。。
依据是JOBID,因为费用分多次结算(NO),最后账单希望得到每次结算的总和和账单号如果是依据JOBID,出来的结果是否应为:
JOBID CHARGE BILL_ID
20 330.00 101/201
35 90.00 102/210
IF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a] ([ID] [int],[JOBID] [int],[NO] [int],[CHARGE] [numeric](5,2),[BILL_NO] [int])
INSERT INTO [a]
SELECT '1','20','1','100.00','101' UNION ALL
SELECT '2','35','1','50.00','102' UNION ALL
SELECT '3','20','1','200.00','101' UNION ALL
SELECT '4','20','2','30.00','201' UNION ALL
SELECT '5','35','2','40.00','210'
-->SQL查询如下:SELECT (select max(JOBID) from a where CHARGE=MAX(t.CHARGE)) JOBID,
SUM(CHARGE) CHARGE,
STUFF((select distinct ','+LTRIM(BILL_NO) from a where NO=t.NO for XML path('')),1,1,'') BILL_NO
FROM [a] t
GROUP BY NO
/*
JOBID CHARGE BILL_NO
----------- --------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20 350.00 101,102
35 70.00 201,210(2 行受影响)
*/
IF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a] ([ID] [int],[JOBID] [int],[NO] [int],[CHARGE] [numeric](5,2),[BILL_NO] [int])
INSERT INTO [a]
SELECT '1','20','1','100.00','101' UNION ALL
SELECT '2','35','1','50.00','102' UNION ALL
SELECT '3','20','1','200.00','101' UNION ALL
SELECT '4','20','2','30.00','201' UNION ALL
SELECT '5','35','2','40.00','210'
-->SQL查询如下:SELECT (select max(JOBID) from a where CHARGE=MAX(t.CHARGE)) JOBID,
SUM(CHARGE) CHARGE,
STUFF((select distinct '/'+LTRIM(BILL_NO) from a where NO=t.NO for XML path('')),1,1,'') BILL_NO
FROM [a] t
GROUP BY NO
/*
JOBID CHARGE BILL_NO
----------- --------------------------------------- ---------------------
20 350.00 101/102
35 70.00 201/210(2 行受影响)*/
估计是楼主把结果的BILL_NO发错了
/*
ID JOBID NO CHARGE BILL_NO
1 20 1 100.00 101
2 35 1 50.00 102
3 20 1 200.00 101
4 20 2 30.00 201
5 35 2 40.00 210
*/
IF OBJECT_ID('Ta') IS NOT NULL
DROP TABLE Ta
GOCREATE TABLE Ta
(
ID INT,
JOBID INT,
[NO] INT,
CHARGE DECIMAL(18,2),
BILL_NO VARCHAR(10)
);INSERT Ta
SELECT 1,20,1,100.00,101 UNION
SELECT 2,35,1,50.00,102 UNION
SELECT 3,20,1,200.00,101 UNION
SELECT 4,20,2,30.00,201 UNION
SELECT 5,35,2,40.00,210
CREATE FUNCTION f_BILLNO(@JOBID INT)
RETURNS VARCHAR(100)
ASBEGIN
DECLARE @BILL_NO VARCHAR(100)
SELECT @BILL_NO=ISNULL(@BILL_NO+'/','')+BILL_NO
FROM(
SELECT DISTINCT BILL_NO
FROM Ta
WHERE JOBID=@JOBID
) a
RETURN @BILL_NO
ENDSELECT JOBID,CHARGE=SUM(ISNULL(CHARGE,0)),BILL_NO=dbo.f_BillNO(JOBID)
FROM Ta
GROUP BY JOBIDJOBID CHARGE BILL_NO
20 330.00 101/201
35 90.00 102/210
this is right
http://www.sqlse.com/forum.php
SELECT jobid, SUM(charge) AS charge,CAST(MIN(bill_no) AS VARCHAR(20)) + '/' + CAST(MAX(bill_no) AS VARCHAR(20)) AS bill_id FROM dbo.taba GROUP BY jobid
SELECT jobid, SUM(charge) AS charge,CAST(MIN(bill_no) AS VARCHAR(20)) + '/' + CAST(MAX(bill_no) AS VARCHAR(20)) AS bill_id FROM dbo.taba GROUP BY jobid