两张表表连接查询。出来的效果是
产品 掺配累积量 掺配比例 掺配精度
红丝 50 0.21 ss 绿丝 60 0.22 ss
怎么改成这种效果,各位大侠帮帮忙。
产品 红丝 绿丝
掺配累积量 50 60
掺配比例 0.21 0.22
掺配精度 ss ss 具体数据库脚本如下SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Infor]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Infor](
[productId] [int] IDENTITY(1,1) NOT NULL,
[productName] [nvarchar](5) NULL,
CONSTRAINT [PK_Infor] PRIMARY KEY CLUSTERED
(
[productId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[test](
[产品ID] [int] NULL,
[掺配累计量] [int] NULL,
[掺配比例] [nvarchar](20) NULL,
[掺配精度] [nvarchar](20) NULL
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_test_Infor]') AND parent_object_id = OBJECT_ID(N'[dbo].[test]'))
ALTER TABLE [dbo].[test] WITH CHECK ADD CONSTRAINT [FK_test_Infor] FOREIGN KEY([产品ID])
REFERENCES [dbo].[Infor] ([productId])
产品 掺配累积量 掺配比例 掺配精度
红丝 50 0.21 ss 绿丝 60 0.22 ss
怎么改成这种效果,各位大侠帮帮忙。
产品 红丝 绿丝
掺配累积量 50 60
掺配比例 0.21 0.22
掺配精度 ss ss 具体数据库脚本如下SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Infor]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Infor](
[productId] [int] IDENTITY(1,1) NOT NULL,
[productName] [nvarchar](5) NULL,
CONSTRAINT [PK_Infor] PRIMARY KEY CLUSTERED
(
[productId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[test](
[产品ID] [int] NULL,
[掺配累计量] [int] NULL,
[掺配比例] [nvarchar](20) NULL,
[掺配精度] [nvarchar](20) NULL
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_test_Infor]') AND parent_object_id = OBJECT_ID(N'[dbo].[test]'))
ALTER TABLE [dbo].[test] WITH CHECK ADD CONSTRAINT [FK_test_Infor] FOREIGN KEY([产品ID])
REFERENCES [dbo].[Infor] ([productId])
解决方案 »
- ====SQL Server 2000,占用了1.7GB的内存,可能是那里出了问题====
- 求一条高级SQL替换语句(内详)
- 關于同步的問題﹐高手請進。
- 有高手会这个问题吗?麻烦写一下代码
- SQL语句:累计求总数,并将它做为一字段
- sql2005上所有数据库的登录从企业器都不见了
- 用户 'NT AUTHORITY\SYSTEM' 登录失败。 原因: 无法打开明确指定的数据库。 [客户端: <local machine>]
- 如何在企业管理器里打开一张表的时候希望记录按某一字段的顺序排列显示
- 急! ! !急! ! !急! ! !关于数据库排序的问题
- Oracle/超级大错误!我的系统快崩溃了...
- 一个数据库查询的问题
- WIN2003+SQL2000(SQL2005)群集,怎么样SQL才能否访问多个盘符?
/*
表tb,数据如下:
项目种类 业绩 提成
洗吹类 200 10
外卖 100 5
合计 300 15
转换成:
项目种类 洗吹类 外卖 合计
业绩 200 100 300
提成 10 5 15
*/create table tb
(
项目种类 varchar(10),
业绩 int,
提成 int
)insert into tb(项目种类,业绩,提成) values('洗吹类',200,10)
insert into tb(项目种类,业绩,提成) values('外卖' ,100,5)
insert into tb(项目种类,业绩,提成) values('合计' ,300,15)
goselect 项目种类,sum(洗吹类) as 洗吹类 , sum(外卖) as 外卖 , sum(合计) as 合计 from
(
select 项目种类 = '业绩',
洗吹类 = case when 项目种类 = '洗吹类' then 业绩 else 0 end,
外卖 = case when 项目种类 = '外卖' then 业绩 else 0 end,
合计 = case when 项目种类 = '合计' then 业绩 else 0 end
from tb
union all
select 项目种类 = '提成' ,
洗吹类 = case when 项目种类 = '洗吹类' then 提成 else 0 end,
外卖 = case when 项目种类 = '外卖' then 提成 else 0 end,
合计 = case when 项目种类 = '合计' then 提成 else 0 end
from tb
) m
group by 项目种类
order by 项目种类 descdrop table tb/*
项目种类 洗吹类 外卖 合计
-------- ----------- ----------- -----------
业绩 200 100 300
提成 10 5 15(所影响的行数为 2 行)
*/--------------------------------------------------------------------------
/*
数据库中tb表格如下
月份 工资 福利 奖金
1月 100 200 300
2月 110 210 310
3月 120 220 320
4月 130 230 330我想得到的结果是项目 1月 2月 3月 4月
工资 100 110 120 130
福利 200 210 220 230
奖金 300 310 320 330就是说完全把表格的行列颠倒,有点像那种旋转矩阵,请问如何用sql 语句实现?
*/if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000) , @s2 varchar(8000),
@s3 varchar(8000) , @s4 varchar(8000),
@s5 varchar(8000) , @i varchar(10)
select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'
select @s1 = @s1 + ',@' + @i + ' varchar(8000)',
@s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
else @new_fdname + '=' end + '''''' + name + '''''''',
@s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname +
']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',
@s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,
@s5 = @s5 + '+'' union all ''+@' + @i,
@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdnameselect @s1=substring(@s1,2,8000),
@s2=substring(@s2,2,8000),
@s4=substring(@s4,2,8000),
@s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '
exec(' + @s5 + ')')
go--用上面的存储过程测试:create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
goexec p_zj 'Test', '月份' , '项目'drop table Test
drop proc p_zj/*
项目 1月 2月 3月 4月
---- ----------- ----------- ----------- -----------
福利 200 210 220 230
工资 100 110 120 130
奖金 300 310 320 330(所影响的行数为 3 行)
*//*
静态写法(SQL2005)
*/
--测试环境
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
go
--测试语句
SELECT * FROM
(
SELECT 考核月份,月份,金额 FROM
(SELECT 月份, 工资, 福利, 奖金 FROM Test) p
UNPIVOT
(金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt
) T
PIVOT
(MAX(金额) FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt--测试结果/*
考核月份 1月 2月 3月 4月
------- ----- ----- ------ -------
福利200210220230
工资100110120130
奖金300310320330
*/--删除环境
Drop table Test
-- AUTHOR : HTL258(TONY)
-- DATE : 2010-04-01 17:29:47
-- VERSION:MICROSOFT SQL SERVER 2008 (SP1) - 10.0.2531.0 (INTEL X86)
-- MAR 29 2009 10:27:29
-- COPYRIGHT (C) 1988-2008 MICROSOFT CORPORATION
-- DEVELOPER EDITION ON WINDOWS NT 5.1 <X86> (BUILD 2600: SERVICE PACK 2)
--------------------------------------------------------------------------
--> 生成测试数据表:TBIF NOT OBJECT_ID('[TB]') IS NULL
DROP TABLE [TB]
GO
CREATE TABLE [TB]([产品] NVARCHAR(10),[掺配累积量] INT,[掺配比例] NVARCHAR(10),[掺配精度] NVARCHAR(10))
INSERT [TB]
SELECT N'红丝',50,'0.21','SS' UNION ALL
SELECT N'绿丝',60,'0.22','SS'
GO
--SELECT * FROM [TB]-->SQL查询如下:SELECT *
FROM (
SELECT 产品 B,CAST([掺配累积量] AS VARCHAR(100)) [掺配累积量],CAST([掺配比例] AS VARCHAR(100)) [掺配比例],CAST([掺配精度] AS VARCHAR(100)) [掺配精度]
FROM TB
) A
UNPIVOT(A FOR 产品 IN([掺配累积量],[掺配比例],[掺配精度])) B
PIVOT(MAX(A) FOR B IN(红丝,绿丝)) C
/*
产品 红丝 绿丝
掺配比例 0.21 0.22
掺配精度 SS SS
掺配累积量 50 60
*/
-- AUTHOR : HTL258(TONY)
-- DATE : 2010-04-01 17:29:47
-- VERSION:MICROSOFT SQL SERVER 2008 (SP1) - 10.0.2531.0 (INTEL X86)
-- MAR 29 2009 10:27:29
-- COPYRIGHT (C) 1988-2008 MICROSOFT CORPORATION
-- DEVELOPER EDITION ON WINDOWS NT 5.1 <X86> (BUILD 2600: SERVICE PACK 2)
--------------------------------------------------------------------------
--> 生成测试数据表:TBIF NOT OBJECT_ID('[TB]') IS NULL
DROP TABLE [TB]
GO
CREATE TABLE [TB]([产品] NVARCHAR(10),[掺配累积量] INT,[掺配比例] NVARCHAR(10),[掺配精度] NVARCHAR(10))
INSERT [TB]
SELECT N'红丝',50,'0.21','SS' UNION ALL
SELECT N'绿丝',60,'0.22','SS'
GO
--SELECT * FROM [TB]-->SQL查询如下:SELECT *
FROM (
SELECT 产品 B,CAST([掺配累积量] AS VARCHAR(100)) [掺配累积量],CAST([掺配比例] AS VARCHAR(100)) [掺配比例],CAST([掺配精度] AS VARCHAR(100)) [掺配精度]
FROM TB
) A
UNPIVOT(A FOR 产品 IN([掺配累积量],[掺配比例],[掺配精度])) B
PIVOT(MAX(A) FOR B IN(红丝,绿丝)) C
ORDER BY CHARINDEX(right(产品,1),'量例度')
/*
产品 红丝 绿丝
掺配累积量 50 60
掺配比例 0.21 0.22
掺配精度 SS SS
*/
--整一下排序
-- Author : happyflystone
-- Date : 2010-04-01
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([产品] NVARCHAR(2),[掺配累积量] NVARCHAR(12),[掺配比例] NVARCHAR(12),[掺配精度] NVARCHAR(12))
Go
INSERT INTO ta
SELECT '红丝','50','0.21','ss' UNION ALL
SELECT '绿丝','60','0.22','ss'
GO
--Start
SELECT *
FROM
(
SELECT [产品],[col1],[col2]
FROM
(select * from ta) p
UNPIVOT
( [col1]
FOR [col2] IN ([掺配累积量] ,[掺配比例] ,[掺配精度])
)AS UNPVT) p
PIVOT
(MAX(col1) FOR [产品] IN(红丝,绿丝)) PVT --Result:
/*
col2 红丝 绿丝
---------------------- ------------ ------------
掺配比例 0.21 0.22
掺配精度 ss ss
掺配累积量 50 60(3 行受影响)
*/
--End
[/code]