34,594
社区成员
发帖
与我相关
我的任务
分享
if object_id('[A]') is not null drop table [A]
go
create table [A]([name] nvarchar(4),[code] int,[栏目] int)
insert [A]
select 'aa',1234,1 union ALL
select 'aa',1234,2 union all
select 'bb',1235,2 union all
select 'cc',1236,3 union all
select 'dd',1237,1 union all
select 'ee',1238,3
if object_id('[B]') is not null drop table [B]
go
create table [B]([code] int,[pv] int)
insert [B]
select 1234,3 union all
select 1236,3 union all
select 1237,2 union all
select 1238,4
---------------------------------------------------------------查询----------------------------------------------------------------------
SELECT c.*,d.pv FROM
(SELECT a.name,a.code,STUFF((select ','+CONVERT(VARCHAR,栏目) FROM [A] b WHERE a.code=b.code FOR XML PATH('')),1,1,'') AS 栏目 FROM [A] a GROUP BY
a.name,a.code)c,[B] d WHERE c.code=d.code
/*
name code 栏目 pv
---- ----------- -------- -----------
aa 1234 1,2 3
cc 1236 3 3
dd 1237 1 2
ee 1238 3 4
(4 行受影响)
*/
select [节目名称name],A.[视频code],[栏目],[播次数 pv] from A right join B on A.[视频code]=B.[视频code]
create table A(
name varchar(10) not null,
code int not null,
[栏目] int not null
)
create table B(
code int not null,
pv int not null
)
insert into A
select 'aa',1234,1 union all
select 'bb',1235,2 union all
select 'cc',1236,3 union all
select 'dd',1237,1 union all
select 'ee',1238,3
insert into B
select 1234,3 union all
select 1236,3 union all
select 1237,2 union all
select 1238,4
select a.name,b.code,a.[栏目],b.pv from A a inner join B b on a.code=b.code
结果:
name code 栏目 pv
aa 1234 1 3
cc 1236 3 3
dd 1237 1 2
ee 1238 3 4----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-05 15:51:13
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([name] nvarchar(4),[code] int,[栏目] int)
insert [A]
select 'aa',1234,1 union all
select 'bb',1235,2 union all
select 'cc',1236,3 union all
select 'dd',1237,1 union all
select 'ee',1238,3
--> 测试数据[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([code] int,[pv] int)
insert [B]
select 1234,3 union all
select 1236,3 union all
select 1237,2 union all
select 1238,4
--------------生成数据--------------------------
select a.* ,b.pv
from [A] INNER JOIN b ON a.code=b.code
----------------结果----------------------------
/*
name code 栏目 pv
---- ----------- ----------- -----------
aa 1234 1 3
cc 1236 3 3
dd 1237 1 2
ee 1238 3 4
*/
select a.name,b.code,a.[栏目],b.pv from A a inner join B b on a.code=b.code