看到一个navy887的专栏 这个帖子,
采用了方法五解决了,但是不懂原理是什么,如有大神请解释,想明白原理!!!
--方法(五)-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-16 09:58:16
if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
Insert #t
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
Go
select [lvl1],
[lvl2],
[lvl3],
[lvl4],
[lvl]=(select min([lvl1])
from (select [lvl1]
union all select [lvl2]
union all select [lvl3]
union all select [lvl4])T)
from #t
/*
lvl1 lvl2 lvl3 lvl4 lvl
----------- ----------- ----------- ----------- -----------
4 3 4 1 1
3 2 2 1 1
2 2 3 4 2
4 4 3 4 3
3 1 2 2 1
采用了方法五解决了,但是不懂原理是什么,如有大神请解释,想明白原理!!!
--方法(五)-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-16 09:58:16
if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
Insert #t
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
Go
select [lvl1],
[lvl2],
[lvl3],
[lvl4],
[lvl]=(select min([lvl1])
from (select [lvl1]
union all select [lvl2]
union all select [lvl3]
union all select [lvl4])T)
from #t
/*
lvl1 lvl2 lvl3 lvl4 lvl
----------- ----------- ----------- ----------- -----------
4 3 4 1 1
3 2 2 1 1
2 2 3 4 2
4 4 3 4 3
3 1 2 2 1
解决方案 »
- 一条SQL 能否实现下面效果?
- 安装完SQL SERVER2000后无法执行添加表命令
- 请问如何用代码建立两个表,并建立它一对多系,并对两个表增删改操作???(sql server2000)
- 那位大哥可以提供MYSQL的下载地址
- SQL Server2000 的数据库怎么移到另一台SQL Server2000呢???
- VIEW SERVER STATE权限怎么开启啊
- 如何把数据插入到同一行中?
- 两个关于FOXBASE2。1的问题(急!急!急!)
- 连接数据库报“常规网络错误”
- 用URL访问SQLSERVER2K
- sql2005自定义函数中不允许执行拼接的SQL语句,是否可以创建clr 函数传入sql 并返回string?
- 求一个sql内容合并
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-02-19 07:46:10
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[T]
if object_id('[T]') is not null drop table [T]
go
create table [T]([Cus_Mobile] bigint,[JOIN_DATE_1] datetime,[JOIN_DATE_2] datetime)
insert [T]
select 13800123545,'2012-01-01','2013-01-01' union all
select 13101055858,'2014-01-01','2014-02-01' union all
select 18912345678,null,'2013-01-02' union all
select 18102012345,null,null
--------------开始查询--------------------------select [Cus_Mobile],CASE WHEN [JOIN_DATE_1]>=[JOIN_DATE_2] THEN [JOIN_DATE_1] ELSE [JOIN_DATE_2] END MAX_JOIN_DATE
from [T]
----------------结果----------------------------
/*
Cus_Mobile MAX_JOIN_DATE
-------------------- -----------------------
13800123545 2013-01-01 00:00:00.000
13101055858 2014-02-01 00:00:00.000
18912345678 2013-01-02 00:00:00.000
18102012345 NULL
*/
(select Cus_Mobile,JOIN_DATE_1 as JOIN_DATE from T
union all
select Cus_Mobile,JOIN_DATE_2 from T) a group by Cus_Mobile