22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [B_Valve] (
[Valve_ID] [int] IDENTITY (1, 1) NOT NULL ,/*ID*/
[Valve_Dim] [smallint] NULL ,/*阀门直径*/
[Valve_kind] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,/*阀门动作类型*/
[Valve_type] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,/*阀门种类*/
[Valve_press] [numeric](4, 2) NULL ,/*阀门压力*/
[Valve_price] [numeric](18, 0) NULL ,/*阀门单价*/
[Year] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL /*阀门报价时间*/
) ON [PRIMARY]
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-06 15:20:05
-- 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)
--
----------------------------------------------------------------
--> 测试数据[B_Valve]
if object_id('[B_Valve]') is not null drop table [B_Valve]
go
create table [B_Valve]([Valve_ID] int,[Valve_Dim] int,[Valve_kind] nvarchar(4),[Valve_type] nvarchar(4),[Valve_press] numeric(2,1),[Valve_price] numeric(3,1),[Year] int)
insert [B_Valve]
select 1,500,N'重锤',N'蝶阀',null,3.5,2014 union all
select 2,600,N'重锤',N'蝶阀',null,4,2014 union all
select 3,700,N'重锤',N'蝶阀',null,4.4,2014 union all
select 4,800,N'重锤',N'蝶阀',null,5.5,2014 union all
select 5,1000,N'重锤',N'蝶阀',null,7.2,2014 union all
select 6,1200,N'重锤',N'蝶阀',null,13,2014 union all
select 7,1250,N'重锤',N'蝶阀',null,15,2014 union all
select 8,1500,N'重锤',N'蝶阀',null,19,2014 union all
select 9,500,N'电动',N'蝶阀',0.6,3,2004 union all
select 10,600,N'电动',N'蝶阀',0.6,4,2004 union all
select 11,700,N'电动',N'蝶阀',0.6,5,2004 union all
select 12,800,N'电动',N'蝶阀',0.6,6,2004 union all
select 13,1000,N'电动',N'蝶阀',0.6,8,2004 union all
select 14,1200,N'电动',N'蝶阀',0.6,11,2004 union all
select 15,1250,N'电动',N'蝶阀',0.6,14,2004 union all
select 16,1500,N'电动',N'蝶阀',0.6,19,2004 union all
select 17,1750,N'电动',N'蝶阀',0.6,24,2004 union all
select 18,500,N'液压',N'蝶阀',0.6,3,2004 union all
select 19,600,N'液压',N'蝶阀',0.6,4,2004 union all
select 20,700,N'液压',N'蝶阀',0.6,6,2004 union all
select 21,800,N'液压',N'蝶阀',0.6,7,2004 union all
select 22,1000,N'液压',N'蝶阀',0.6,9,2004 union all
select 23,1200,N'液压',N'蝶阀',0.6,12,2004 union all
select 24,1250,N'液压',N'蝶阀',0.6,15,2004 union all
select 25,1500,N'液压',N'蝶阀',0.6,21,2004 union all
select 26,1750,N'液压',N'蝶阀',0.6,27,2004
--------------生成数据--------------------------
SELECT MAX([Valve_ID])[Valve_ID],[Valve_Dim],[Valve_type],[year],MAX([Valve_price])[Valve_price]
FROM [B_Valve] a
WHERE EXISTS (SELECT 1 FROM (
select [Valve_Dim],[Valve_type],MAX([year])[year],MAX([Valve_price])[Valve_price]
from [B_Valve]
WHERE [Valve_type] IN (N'蝶阀',N'球阀')
GROUP BY [Valve_Dim],[Valve_type]) b WHERE a.[Valve_Dim]=b.[Valve_Dim] AND a.[Valve_type]=b.[Valve_type] AND a.[year]=b.[year])
GROUP BY [Valve_Dim],[Valve_type],[year]
----------------结果----------------------------
/*
Valve_ID Valve_Dim Valve_type year Valve_price
----------- ----------- ---------- ----------- ---------------------------------------
1 500 蝶阀 2014 3.5
2 600 蝶阀 2014 4.0
3 700 蝶阀 2014 4.4
4 800 蝶阀 2014 5.5
5 1000 蝶阀 2014 7.2
6 1200 蝶阀 2014 13.0
7 1250 蝶阀 2014 15.0
8 1500 蝶阀 2014 19.0
26 1750 蝶阀 2004 27.0
*/
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-06 15:20:05
-- 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)
--
----------------------------------------------------------------
--> 测试数据[B_Valve]
if object_id('[B_Valve]') is not null drop table [B_Valve]
go
create table [B_Valve]([Valve_ID] int,[Valve_Dim] int,[Valve_kind] nvarchar(4),[Valve_type] nvarchar(4),[Valve_press] numeric(2,1),[Valve_price] numeric(3,1),[Year] int)
insert [B_Valve]
select 1,500,N'重锤',N'蝶阀',null,3.5,2014 union all
select 2,600,N'重锤',N'蝶阀',null,4,2014 union all
select 3,700,N'重锤',N'蝶阀',null,4.4,2014 union all
select 4,800,N'重锤',N'蝶阀',null,5.5,2014 union all
select 5,1000,N'重锤',N'蝶阀',null,7.2,2014 union all
select 6,1200,N'重锤',N'蝶阀',null,13,2014 union all
select 7,1250,N'重锤',N'蝶阀',null,15,2014 union all
select 8,1500,N'重锤',N'蝶阀',null,19,2014 union all
select 9,500,N'电动',N'蝶阀',0.6,3,2004 union all
select 10,600,N'电动',N'蝶阀',0.6,4,2004 union all
select 11,700,N'电动',N'蝶阀',0.6,5,2004 union all
select 12,800,N'电动',N'蝶阀',0.6,6,2004 union all
select 13,1000,N'电动',N'蝶阀',0.6,8,2004 union all
select 14,1200,N'电动',N'蝶阀',0.6,11,2004 union all
select 15,1250,N'电动',N'蝶阀',0.6,14,2004 union all
select 16,1500,N'电动',N'蝶阀',0.6,19,2004 union all
select 17,1750,N'电动',N'蝶阀',0.6,24,2004 union all
select 18,500,N'液压',N'蝶阀',0.6,3,2004 union all
select 19,600,N'液压',N'蝶阀',0.6,4,2004 union all
select 20,700,N'液压',N'蝶阀',0.6,6,2004 union all
select 21,800,N'液压',N'蝶阀',0.6,7,2004 union all
select 22,1000,N'液压',N'蝶阀',0.6,9,2004 union all
select 23,1200,N'液压',N'蝶阀',0.6,12,2004 union all
select 24,1250,N'液压',N'蝶阀',0.6,15,2004 union all
select 25,1500,N'液压',N'蝶阀',0.6,21,2004 union all
select 26,1750,N'液压',N'蝶阀',0.6,27,2004
--------------生成数据--------------------------
SELECT *
FROM [B_Valve] a
WHERE EXISTS (SELECT 1 FROM (
select [Valve_Dim],[Valve_type],MAX([year])[year],MAX([Valve_price])[Valve_price]
from [B_Valve]
WHERE [Valve_type] IN (N'蝶阀',N'球阀')
GROUP BY [Valve_Dim],[Valve_type]) b WHERE a.[Valve_Dim]=b.[Valve_Dim] AND a.[Valve_type]=b.[Valve_type] AND a.[year]=b.[year] AND a.[Valve_price]=b.[Valve_price])
----------------结果----------------------------
/*
Valve_ID Valve_Dim Valve_kind Valve_type Valve_press Valve_price Year
----------- ----------- ---------- ---------- --------------------------------------- --------------------------------------- -----------
1 500 重锤 蝶阀 NULL 3.5 2014
2 600 重锤 蝶阀 NULL 4.0 2014
6 1200 重锤 蝶阀 NULL 13.0 2014
7 1250 重锤 蝶阀 NULL 15.0 2014
26 1750 液压 蝶阀 0.6 27.0 2004
*/
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-06 15:20:05
-- 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)
--
----------------------------------------------------------------
--> 测试数据[B_Valve]
if object_id('[B_Valve]') is not null drop table [B_Valve]
go
create table [B_Valve]([Valve_ID] int,[Valve_Dim] int,[Valve_kind] nvarchar(4),[Valve_type] nvarchar(4),[Valve_press] numeric(2,1),[Valve_price] numeric(3,1),[Year] int)
insert [B_Valve]
select 1,500,N'重锤',N'蝶阀',null,3.5,2014 union all
select 2,600,N'重锤',N'蝶阀',null,4,2014 union all
select 3,700,N'重锤',N'蝶阀',null,4.4,2014 union all
select 4,800,N'重锤',N'蝶阀',null,5.5,2014 union all
select 5,1000,N'重锤',N'蝶阀',null,7.2,2014 union all
select 6,1200,N'重锤',N'蝶阀',null,13,2014 union all
select 7,1250,N'重锤',N'蝶阀',null,15,2014 union all
select 8,1500,N'重锤',N'蝶阀',null,19,2014 union all
select 9,500,N'电动',N'蝶阀',0.6,3,2004 union all
select 10,600,N'电动',N'蝶阀',0.6,4,2004 union all
select 11,700,N'电动',N'蝶阀',0.6,5,2004 union all
select 12,800,N'电动',N'蝶阀',0.6,6,2004 union all
select 13,1000,N'电动',N'蝶阀',0.6,8,2004 union all
select 14,1200,N'电动',N'蝶阀',0.6,11,2004 union all
select 15,1250,N'电动',N'蝶阀',0.6,14,2004 union all
select 16,1500,N'电动',N'蝶阀',0.6,19,2004 union all
select 17,1750,N'电动',N'蝶阀',0.6,24,2004 union all
select 18,500,N'液压',N'蝶阀',0.6,3,2004 union all
select 19,600,N'液压',N'蝶阀',0.6,4,2004 union all
select 20,700,N'液压',N'蝶阀',0.6,6,2004 union all
select 21,800,N'液压',N'蝶阀',0.6,7,2004 union all
select 22,1000,N'液压',N'蝶阀',0.6,9,2004 union all
select 23,1200,N'液压',N'蝶阀',0.6,12,2004 union all
select 24,1250,N'液压',N'蝶阀',0.6,15,2004 union all
select 25,1500,N'液压',N'蝶阀',0.6,21,2004 union all
select 26,1750,N'液压',N'蝶阀',0.6,27,2004
--------------生成数据--------------------------
--SELECT *
--FROM [B_Valve] a
--WHERE EXISTS (SELECT 1 FROM (
--select [Valve_Dim],[Year],MAX([Valve_price])[Valve_price]
--from [B_Valve]
--WHERE [Valve_type] IN (N'蝶阀',N'球阀')
--GROUP BY [Valve_Dim],[Year]) b WHERE a.valve_dim=b.valve_dim AND a.[year]=b.[year] AND a.[Valve_price]=b.[Valve_price])
--AND [Valve_type] IN (N'蝶阀',N'球阀')
select [Valve_Dim],[Valve_type],MAX([year])[year],MAX([Valve_price])[Valve_price]
from [B_Valve]
WHERE [Valve_type] IN (N'蝶阀',N'球阀')
GROUP BY [Valve_Dim],[Valve_type]
----------------结果----------------------------
/*
Valve_Dim Valve_type year Valve_price
----------- ---------- ----------- ---------------------------------------
500 蝶阀 2014 3.5
600 蝶阀 2014 4.0
700 蝶阀 2014 6.0
800 蝶阀 2014 7.0
1000 蝶阀 2014 9.0
1200 蝶阀 2014 13.0
1250 蝶阀 2014 15.0
1500 蝶阀 2014 21.0
1750 蝶阀 2004 27.0
*/
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-06 15:20:05
-- 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)
--
----------------------------------------------------------------
--> 测试数据[B_Valve]
if object_id('[B_Valve]') is not null drop table [B_Valve]
go
create table [B_Valve]([Valve_ID] int,[Valve_Dim] int,[Valve_kind] nvarchar(4),[Valve_type] nvarchar(4),[Valve_press] numeric(2,1),[Valve_price] numeric(3,1),[Year] int)
insert [B_Valve]
select 1,500,N'重锤',N'蝶阀',null,3.5,2014 union all
select 2,600,N'重锤',N'蝶阀',null,4,2014 union all
select 3,700,N'重锤',N'蝶阀',null,4.4,2014 union all
select 4,800,N'重锤',N'蝶阀',null,5.5,2014 union all
select 5,1000,N'重锤',N'蝶阀',null,7.2,2014 union all
select 6,1200,N'重锤',N'蝶阀',null,13,2014 union all
select 7,1250,N'重锤',N'蝶阀',null,15,2014 union all
select 8,1500,N'重锤',N'蝶阀',null,19,2014 union all
select 9,500,N'电动',N'蝶阀',0.6,3,2004 union all
select 10,600,N'电动',N'蝶阀',0.6,4,2004 union all
select 11,700,N'电动',N'蝶阀',0.6,5,2004 union all
select 12,800,N'电动',N'蝶阀',0.6,6,2004 union all
select 13,1000,N'电动',N'蝶阀',0.6,8,2004 union all
select 14,1200,N'电动',N'蝶阀',0.6,11,2004 union all
select 15,1250,N'电动',N'蝶阀',0.6,14,2004 union all
select 16,1500,N'电动',N'蝶阀',0.6,19,2004 union all
select 17,1750,N'电动',N'蝶阀',0.6,24,2004 union all
select 18,500,N'液压',N'蝶阀',0.6,3,2004 union all
select 19,600,N'液压',N'蝶阀',0.6,4,2004 union all
select 20,700,N'液压',N'蝶阀',0.6,6,2004 union all
select 21,800,N'液压',N'蝶阀',0.6,7,2004 union all
select 22,1000,N'液压',N'蝶阀',0.6,9,2004 union all
select 23,1200,N'液压',N'蝶阀',0.6,12,2004 union all
select 24,1250,N'液压',N'蝶阀',0.6,15,2004 union all
select 25,1500,N'液压',N'蝶阀',0.6,21,2004 union all
select 26,1750,N'液压',N'蝶阀',0.6,27,2004
--------------生成数据--------------------------
SELECT *
FROM [B_Valve] a
WHERE EXISTS (SELECT 1 FROM (
select [Valve_Dim],[Year],MAX([Valve_price])[Valve_price]
from [B_Valve]
WHERE [Valve_type] IN (N'蝶阀',N'球阀')
GROUP BY [Valve_Dim],[Year]) b WHERE a.valve_dim=b.valve_dim AND a.[year]=b.[year] AND a.[Valve_price]=b.[Valve_price])
AND [Valve_type] IN (N'蝶阀',N'球阀')
----------------结果----------------------------
/*
Valve_ID Valve_Dim Valve_kind Valve_type Valve_press Valve_price Year
----------- ----------- ---------- ---------- --------------------------------------- --------------------------------------- -----------
1 500 重锤 蝶阀 NULL 3.5 2014
2 600 重锤 蝶阀 NULL 4.0 2014
3 700 重锤 蝶阀 NULL 4.4 2014
4 800 重锤 蝶阀 NULL 5.5 2014
5 1000 重锤 蝶阀 NULL 7.2 2014
6 1200 重锤 蝶阀 NULL 13.0 2014
7 1250 重锤 蝶阀 NULL 15.0 2014
8 1500 重锤 蝶阀 NULL 19.0 2014
9 500 电动 蝶阀 0.6 3.0 2004
10 600 电动 蝶阀 0.6 4.0 2004
18 500 液压 蝶阀 0.6 3.0 2004
19 600 液压 蝶阀 0.6 4.0 2004
20 700 液压 蝶阀 0.6 6.0 2004
21 800 液压 蝶阀 0.6 7.0 2004
22 1000 液压 蝶阀 0.6 9.0 2004
23 1200 液压 蝶阀 0.6 12.0 2004
24 1250 液压 蝶阀 0.6 15.0 2004
25 1500 液压 蝶阀 0.6 21.0 2004
26 1750 液压 蝶阀 0.6 27.0 2004
*/