Select min(a.telephone) as telephone,
count(*) as user,
sum(a.accounts)as accounts,
sum(isnull(fee01,0)) as fee01,
sum(isnull(fee02,0)) as fee02,
sum(isnull(fee03,0)) as fee03
from
userlist a left join charger b on a.telephone=b.telephone
group by a.rent
select a.*,b.feeo1,b.fee02,b.fee03 from @userlist a
join @charger b on a.telephone=b.telephone
INSERT @TA
SELECT 10041, 'aaaa', 12.05 UNION ALL
SELECT 10042, 'aaaa', 22.12 UNION ALL
SELECT 10043, 'ccc', 33 UNION ALL
SELECT 10044, 'bbb', 25.01DECLARE @TB TABLE([telephone] INT, [fee01] INT, [fee02] INT, [fee03] INT)
INSERT @TB
SELECT 10041, 11, 22, 9 UNION ALL
SELECT 10042, 12, 10, 10 UNION ALL
SELECT 10043, 24, 33, 12SELECT A.telephone,[user],rent,[accounts],ISNULL(fee01,0) AS fee01,ISNULL(fee02,0) as fee02,ISNULL(fee03,0) as fee03
FROM (
SELECT rent,min(telephone) as telephone,sum([accounts]) as [accounts],COUNT(*) AS [user]
FROM @TA
GROUP BY rent
) A LEFT JOIN @TB AS B
ON A.[telephone]=B.[telephone]
/*
telephone user rent accounts fee01 fee02 fee03
----------- ----------- ---- --------------------------------------- ----------- ----------- -----------
10041 2 aaaa 34.17 11 22 9
10044 1 bbb 25.01 0 0 0
10043 1 ccc 33.00 24 33 12
*/
INSERT userlist
SELECT 10041, 'aaaa', 12.05 UNION ALL
SELECT 10042, 'aaaa', 22.12 UNION ALL
SELECT 10043, 'ccc', 33 UNION ALL
SELECT 10044, 'bbb', 25.01CREATE TABLE charger ([telephone] INT, [fee01] INT, [fee02] INT, [fee03] INT)
INSERT charger
SELECT 10041, 11, 22, 9 UNION ALL
SELECT 10042, 12, 10, 10 UNION ALL
SELECT 10043, 24, 33, 12
Select telephone=min(a.telephone),
[user]=count(*),
accounts=sum(a.accounts),
fee01=sum(isnull(fee01,0)),
fee02=sum(isnull(fee02,0)) ,
fee03=sum(isnull(fee03,0))
from userlist a left join charger b on a.telephone=b.telephone
group by a.rent
ORDER BY telephonetelephone user accounts fee01 fee02 fee03
----------- ----------- --------------------------------------- ----------- ----------- -----------
10041 2 34.17 23 32 19
10043 1 33.00 24 33 12
10044 1 25.01 0 0 0(3 行受影响)
if object_id('tb1')is not null drop table tb1
create table tb1 (telephone char(5),rent varchar(4),accounts decimal(4,2))
insert tb1
select '10041','aaaa',12.05 union all
select '10042','aaaa',22.12 union all
select '10043','ccc',33 union all
select '10044','bbb',25.01if object_id('tb2') is not null drop table tb2
create table tb2(telephone char(5),fee01 int,fee02 int,fee03 int)
insert tb2
select '10041',11,22,9 union all
select '10042',12,10,10 union all
select '10043',24,33,12select * from tb1 a left join tb2 b on a.telephone=b.telephone Select telephone=min(a.telephone),
[user]=count(*),
accounts=sum(a.accounts),
fee01=sum(isnull(fee01,0)),
fee02=sum(isnull(fee02,0)) ,
fee03=sum(isnull(fee03,0))
from tb1 a left join tb2 b on a.telephone=b.telephone
group by a.rent
ORDER BY telephone
/*------------------------------
10041 2 34.17 23 32 19
10043 1 33.00 24 33 12
10044 1 25.01 0 0 0
----------------------------------
*/
INSERT @TA
SELECT 10041, 'aaaa', 12.05 UNION ALL
SELECT 10042, 'aaaa', 22.12 UNION ALL
SELECT 10043, 'ccc', 33 UNION ALL
SELECT 10044, 'bbb', 25.01DECLARE @TB TABLE([telephone] INT, [fee01] INT, [fee02] INT, [fee03] INT)
INSERT @TB
SELECT 10041, 11, 22, 9 UNION ALL
SELECT 10042, 12, 10, 10 UNION ALL
SELECT 10043, 24, 33, 12
Select telephone=min(a.telephone),
[user]=count(*),
accounts=sum(a.accounts),
fee01=sum(isnull(fee01,0)),
fee02=sum(isnull(fee02,0)) ,
fee03=sum(isnull(fee03,0))
from @ta a left join @tb b on a.telephone=b.telephone
group by a.rent
ORDER BY telephone/*
10041 2 34.17 23 32 19
10043 1 33.00 24 33 12
10044 1 25.01 0 0 0
*/
[user]=count(*),
accounts=sum(a.accounts),
fee01=sum(isnull(fee01,0)),
fee02=sum(isnull(fee02,0)) ,
fee03=sum(isnull(fee03,0))
from tb1 a left join tb2 b on a.telephone=b.telephone
group by a.rent
ORDER BY telephone
create table userlist
(
telephone varchar(10),
rent varchar(20),
accounts decimal(7,2)
)
insert into userlist
select '10041','aaaa',12.055
union all
select '10042','aaaa',22.258
union all
select '10043','bbbb',33.22
union all
select '10044','cccc',27.268
create table charger
(
telephone varchar(10),
fee01 int,
fee02 int ,
fee03 int
)
insert into charger
select '10041',11,22,9
union all
select '10043',12,10,10
union all
select '10044',24,33,12
select t1.*,t2.fee01,t2.fee02,t2.fee03 from
(
select min(telephone) as telephone ,rent,count(rent) as users ,sum(accounts) as accounts from userlist group by rent
) t1
inner join
charger t2
on t1.telephone=t2.telephone
---------- -------------------- ----------- ---------------------------------------- 10041 aaaa 2 34.32 11 22 9
10043 bbbb 1 33.22 12 10 10
10044 cccc 1 27.27 24 33 12