单价表P_price
name price
电脑 3000
冰箱 1000
手机 2000详细表P_detalis
id userid name count
1 1 电脑 10
2 1 手机 2
3 2 手机 3要想显示的结果是:(条件是userid为1的用户的销售情况)
userid name count
1 电脑 10
1 冰箱 0
1 手机 2请问高手如果解决该问题?
name price
电脑 3000
冰箱 1000
手机 2000详细表P_detalis
id userid name count
1 1 电脑 10
2 1 手机 2
3 2 手机 3要想显示的结果是:(条件是userid为1的用户的销售情况)
userid name count
1 电脑 10
1 冰箱 0
1 手机 2请问高手如果解决该问题?
from P_detalis a
join P_price b
on a.name=b.name
gorup by a.userid,a.name
还有个userid=1的条件哦。
set @userid=1
select @userid,a.name,isnull(b.cnt,0) as [count]
from p_price a
left join
(select name,sum([count]) as [count]
from P_detalis
where userid=@userid
group by name) b
on a.name=b.name
if object_id('[P_price]') is not null drop table [P_price]
create table [P_price]([name] varchar(4),[price] int)
go
insert [P_price]
select '电脑',3000 union all
select '冰箱',1000 union all
select '手机',2000
--> 测试数据:[P_detalis]
if object_id('[P_detalis]') is not null drop table [P_detalis]
create table [P_detalis]([id] int,[userid] int,[name] varchar(4),[count] int)
go
insert [P_detalis]
select 1,1,'电脑',10 union all
select 2,1,'手机',2 union all
select 3,2,'手机',3select t.*,isnull(r.count,0) as count
from
(select 1 as [userid],*
from [P_price]) t
left join [P_detalis] r
on t.[userid] = r.[userid] and t.[name] = r.[name]userid name price count
----------- ---- ----------- -----------
1 电脑 3000 10
1 冰箱 1000 0
1 手机 2000 2(3 行受影响)
-- Author :SQL77(只为思齐老)
-- Date :2010-04-29 09:22:45
-- Version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
-- Oct 14 2005 00:33:37
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#P_price
if object_id('tempdb.dbo.#P_price') is not null drop table #P_price
go
create table #P_price([name] varchar(4),[price] int)
insert #P_price
select '电脑',3000 union all
select '冰箱',1000 union all
select '手机',2000
--> 测试数据:#P_detalis
if object_id('tempdb.dbo.#P_detalis') is not null drop table #P_detalis
go
create table #P_detalis([id] int,[userid] int,[name] varchar(4),[count] int)
insert #P_detalis
select 1,1,'电脑',10 union all
select 2,1,'手机',2 union all
select 3,2,'手机',3
--------------开始查询--------------------------
select
pd.[userid],
pp.name,
isnull(pd.[count],0) [count]
from
#P_price pp left join #P_detalis pd on pp.name=pd.name
and userid=1
----------------结果----------------------------
/* (3 行受影响)(3 行受影响)
userid name count
----------- ---- -----------
1 电脑 10
NULL 冰箱 0
1 手机 2(3 行受影响)
*/
name varchar(20),
price Decimal(28, 2)
)
Declare @P_detalis Table(
id int,
userid int,
name varchar(20),
[count] int
)Insert @P_price(name, price)
Select '电脑', 3000
Union All
Select '冰箱', 1000
Union All
Select '手机', 2000Insert @P_detalis(id, userid, name, [count])
Select 1, 1, '电脑', 10
Union All
Select 2, 1, '手机', 2
Union All
Select 3, 2, '手机', 3Select IsNull(d.userid, 1) As userid, p.name, IsNull(d.[count], 0) As [count]
From @P_price p Left Join @P_detalis d
On p.name = d.name And d.userid = 1
-- Author : htl258(Tony)
-- Date : 2010-04-29 09:25:01
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:P_priceIF NOT OBJECT_ID('[P_price]') IS NULL
DROP TABLE [P_price]
GO
CREATE TABLE [P_price]([name] NVARCHAR(10),[price] INT)
INSERT [P_price]
SELECT N'电脑',3000 UNION ALL
SELECT N'冰箱',1000 UNION ALL
SELECT N'手机',2000
GO
--SELECT * FROM [P_price]--> 生成测试数据表:P_detalisIF NOT OBJECT_ID('[P_detalis]') IS NULL
DROP TABLE [P_detalis]
GO
CREATE TABLE [P_detalis]([id] INT,[userid] INT,[name] NVARCHAR(10),[count] INT)
INSERT [P_detalis]
SELECT 1,1,N'电脑',10 UNION ALL
SELECT 2,1,N'手机',2 UNION ALL
SELECT 3,2,N'手机',3
GO
--SELECT * FROM [P_detalis]-->SQL查询如下:
select a.userid,a.name, isnull([count],0) [count]
from (select 1 userid, * from [P_price]) a
left join [P_detalis] b
on a.userid=b.userid and a.name=b.name/*
userid name count
----------- ---------- -----------
1 电脑 10
1 冰箱 0
1 手机 2(3 行受影响)
*/不好意思,理解错了
go
create table #P_price([name] varchar(4),[price] int)
insert #P_price
select '电脑',3000 union all
select '冰箱',1000 union all
select '手机',2000if object_id('tempdb.dbo.#P_detalis') is not null drop table #P_detalis
go
create table #P_detalis([id] int,[userid] int,[name] varchar(4),[count] int)
insert #P_detalis
select 1,1,'电脑',10 union all
select 2,1,'手机',2 union all
select 3,2,'手机',3select isnull(D.[userid],1) [userid],P.[name],isnull(D.[count],0) [count]
from
#P_price P
left join
(select * from #P_detalis where [userid]=1)
D
on P.[name]=D.[name]userid name count
----------- ---- -----------
1 电脑 10
1 冰箱 0
1 手机 2(3 行受影响)
select
isnull(d.userid,1) as userid,
p.name,
isnull(d.count ,0) as count
from P_price p left join P_detalis d on p.name = d.name and d.userid=1userid name count
----------- ---- -----------
1 电脑 10
1 冰箱 0
1 手机 2(所影响的行数为 3 行)
针对所有数据
select
isnull(d.userid,1) as userid,
p.name,
sum(isnull(d.count ,0)) as count
from P_price p left join P_detalis d on p.name = d.name and d.userid=1
group by isnull(d.userid,1),p.nameuserid name count
----------- ---- -----------
1 冰箱 0
1 电脑 10
1 手机 2(所影响的行数为 3 行)
就可以了:if object_id('[P_detalis]') is not null drop table [P_detalis]
create table [P_detalis]([id] int,[userid] int,[name] varchar(4),[count] int)
go
insert [P_detalis]
select 1,1,'电脑',10 union all
select 2,1,'手机',2 union all
select 3,2,'手机',3select * from p_detalisselect min(userid) as userid ,name ,sum(count) as count from p_detalis where userid = 1 group by name