SELECT ONE.ID,ONE.[NAME],TWO.PRICE FROM ONE INNER JOIN TWO ON ONE.ID=TWO.ONEID
select a.*, sum(isnull(b.price,0)) from one a left join two b on a.id=b.oneid group by a.id , a.name
SELECT T1.ID,T1.[NAME],COALESCE(T2.MAXPRICE,0) FROM ONE T1 LEFT JOIN ( SELECT ONEID,MAX(PRICE) 'MAXPRICE' FROM TWO --如果求和就是SUM ) T2 ON T1.ID=T2.ONEID
--> Title : Generating test data #one --> Author : wufeng4552 --> Date : 2009-11-18 15:08:41 if object_id('tempdb.dbo.#one') is not null drop table #one go create table #one (id int,name nvarchar(4)) insert into #one select 1,N'移动' union all select 2,N'电信' union all select 3,N'联通' --> Title : Generating test data #two --> Author : wufeng4552 --> Date : 2009-11-18 15:08:41 if object_id('tempdb.dbo.#two') is not null drop table #two go create table #two (oneid int,price numeric(6,2)) insert into #two select 1,4000.00 union all select 3,200.00 union all select 3,5000.00 select a.*, sum(isnull(b.price,0)) from #one a left join #two b on a.id=b.oneid group by a.id , a.name /* id name ----------- ---- --------------------------------------- 1 移动 4000.00 2 电信 0.00 3 联通 5200.00(3 個資料列受到影響)*/
SELECT T.ID, T.[name], SUM([price]) FROM @TB T LEFT JOIN @TB1 T1 ON T.[id] = T1.[oneid] GROUP BY T.ID, T.[name] ORDER BY T.ID/* ID name ----------- ---- --------------------------------------- 1 移动 4000.00 2 电信 NULL 3 联通 5200.00
select a.*,isnull((b.price),0) as price from one a left join (select oneid,sum(price) as price from two group by oneid)b on a.id=b.oneid
select id 编号,[name] 名称,price 价格 from tb1 left join tb2 on tb1.id=tb2.oneid
select id 编号,[name] 名称,max(price) 价格 from tb1 left join tb2 on tb1.id=tb2.oneid group by id,name
CREATE TABLE ONE( Id INT PRIMARY KEY, NAME CHAR(4) NOT NULL UNIQUE);CREATE TABLE TWO( Id INT PRIMARY KEY, PRICE DOUBLE Precisiion, FOREIGN KEY(Id) REFERENCES ONE(Id) );INSERT INTO ONE(Id,NAME) SELECT 1,'移动' UNION ALL SELECT 2,'电信' UNION ALL SELECT 3,'联通'INSERT INTO two(Id,price) SELECT 1,4000.00 UNION ALL SELECT 2,200.00 UNION ALL SELECT 3,5000.00SELECT DISTINCT ONE.Id,NAME,PRICE FROM ONE,TWO WHERE ONE.Id=TWO.Id ORDER BY ONE.Id
select id 编号,name 名称, price 价格 from one inner join two on id = oneid
FROM ONE
INNER JOIN TWO ON ONE.ID=TWO.ONEID
sum(isnull(b.price,0))
from one a left join two b
on a.id=b.oneid
group by a.id , a.name
FROM ONE T1
LEFT JOIN (
SELECT ONEID,MAX(PRICE) 'MAXPRICE' FROM TWO --如果求和就是SUM
) T2 ON T1.ID=T2.ONEID
--> Author : wufeng4552
--> Date : 2009-11-18 15:08:41
if object_id('tempdb.dbo.#one') is not null drop table #one
go
create table #one (id int,name nvarchar(4))
insert into #one
select 1,N'移动' union all
select 2,N'电信' union all
select 3,N'联通'
--> Title : Generating test data #two
--> Author : wufeng4552
--> Date : 2009-11-18 15:08:41
if object_id('tempdb.dbo.#two') is not null drop table #two
go
create table #two (oneid int,price numeric(6,2))
insert into #two
select 1,4000.00 union all
select 3,200.00 union all
select 3,5000.00
select a.*,
sum(isnull(b.price,0))
from #one a left join #two b
on a.id=b.oneid
group by a.id , a.name
/*
id name
----------- ---- ---------------------------------------
1 移动 4000.00
2 电信 0.00
3 联通 5200.00(3 個資料列受到影響)*/
SELECT T.ID, T.[name], SUM([price])
FROM @TB T LEFT JOIN @TB1 T1 ON T.[id] = T1.[oneid]
GROUP BY T.ID, T.[name]
ORDER BY T.ID/*
ID name
----------- ---- ---------------------------------------
1 移动 4000.00
2 电信 NULL
3 联通 5200.00
a.*,isnull((b.price),0) as price
from
one a
left join
(select oneid,sum(price) as price from two group by oneid)b
on
a.id=b.oneid
from tb1 left join tb2
on tb1.id=tb2.oneid
from tb1 left join tb2
on tb1.id=tb2.oneid
group by id,name
Id INT PRIMARY KEY,
NAME CHAR(4) NOT NULL UNIQUE);CREATE TABLE TWO(
Id INT PRIMARY KEY,
PRICE DOUBLE Precisiion,
FOREIGN KEY(Id) REFERENCES ONE(Id)
);INSERT
INTO ONE(Id,NAME)
SELECT 1,'移动'
UNION ALL SELECT 2,'电信'
UNION ALL SELECT 3,'联通'INSERT
INTO two(Id,price)
SELECT 1,4000.00
UNION ALL SELECT 2,200.00
UNION ALL SELECT 3,5000.00SELECT DISTINCT ONE.Id,NAME,PRICE
FROM ONE,TWO
WHERE ONE.Id=TWO.Id
ORDER BY ONE.Id