leadbbs 上ID为Robin·H的东西,有空瞧瞧....

1.
表结构是
ID state
1  1,5,17
2  2,8,10,17
3  2,5
......

如何查询出state中逗号分割后,指定数字的行?
例如查state中含5的行?

答
SELECT * FROM [table] WHERE CHARINDEX(',5,' , ','+[state]+',')>0
-----------------------------------------------------------------------

2
比如说，我两张表

表A是一个企业的基本信息表
字段：
id    cor_id    cor_name

表B是企业发送消息的列表

id    cor_id    message

 两张表用企业用户名相关联 ，cor-id相管理

  我如何查询出最新15个发送消息的企业的名称呢？

答
SELECT cor_name FROM cor WHERE cor_id IN (SELECT TOP 3 cor_id FROM mes GROUP BY cor_id ORDER BY MAX(ID) DESC)
------------------------------------------------------------
3
我想自动生成了一个log_no  是由SHA+year(06)+month(06)+日+(三位序号)001
现在这个功能我能实现  
但是在删除时，怎么样使得这个log_no连续呢？  
 
 
谢谢了!!!!!!!!!!!!!!!!!
答
DECLARE @ID VARCHAR(9)
SET @ID='060609008'
DELETE FROM TABLE WHERE ID=@ID
UPDATE Table
SET ID=LEFT(ID,6) + REPLICATE(0,3-LEN(CAST(SUBSTRING(ID,7,3) AS INT)-1)) + RTRIM(CAST(CAST(SUBSTRING(ID,7,3) AS INT)-1) AS CHAR(3))
WHERE LEFT(ID,6)=LEFT(@ID,6) AND CAST(SUBSTRING(ID,7,3) AS INT)>CAST(SUBSTRING(@ID,7,3) AS INT)
------------------------------------------------------------
4
如何按价格分时间段求和?
有表table1,表中数据如下
table1

Price   Date
1 2006-5-1
1 2006-5-2
1.5 2006-5-3
1.5 2006-5-4
1.5 2006-5-6
1 2006-5-7


结果:
TOTAL
2 2006-5-1-----2006-5-2
4.5 2006-5-3-----2006-5-6
1 2006-5-7-----2006-5-7
反应出价格的波动时间段了
所有数据都是按日期排好序的（不一定连续）

答
生成临时数据
----------------------------
select
1 ,'2006-5-1' union all select
1 ,'2006-5-2' union all select
1.5 ,'2006-5-3' union all select
1.5 ,'2006-5-4' union all select
1.5 ,'2006-5-6' union all select
1 ,'2006-5-7' union all select
1,'2006-5-8' union all select
2,'2006-5-9'

存入临时表#tb,目的是生成唯一的标识号id,以便后面处理
-------------------------------------------------
select price,date,identity(int) id into #tb from @t


查询所需数据.
--------------------------------------------
select id,price,date,hasBefore=(select count(1) from #tb b where b.price=a.price and b.id=a.id-1)  into #base from #tb a
select price,date=min(date) + '--' + max(date) from (select price ,date,far=(case hasBefore when 0 then id else (select max(x.id) from #base x where x.price=b.price and x.id<b.id and x.hasBefore=0) end) from #base b) base group by price,far


显示结果
-------------------------------
1.02006-5-1--2006-5-2
1.52006-5-3--2006-5-6
1.02006-5-7--2006-5-8
2.02006-5-9--2006-5-9

-------------------------------------------------------------------------------

5
我有一张几十万条记录的表,其中有company,address,companyid三个字段。
现在想将company,address相同内容记录的companyid更新成一个相同的。
以前companyid都不相同。不想用循环更新，因为那样速度太慢了，
哪位大哥能通过一条语句实现上述功能啊。主要是速度。小弟先谢谢了

答
update tbb set companyid=id from (select min(companyid) id,company c,address a from tbb group by company,address) base where a=address and c=company
----------------------------------------------------------------------------------
6
在表连接的SELECT语句中经常要对引用的列加上各自的表别名，例如：
SELECT a.colx,b.coly FROM table1 AS a ,table2 AS b WHERE a.name = b.name
但是，不能再对含有表别名的列赋常量值，例如不能这样：
SELECT a.colx = 常量,b.coly FROM table1 AS a,table2 AS b WHERE a.name = b.name
甚至也不能这样：
SELECT a.colx = 常量 FROM table1 AS a

请教大家解决的办法，多谢！

答
加标识符就可以了.
[a.aname]='xxxx'
--------------------------------------------------------------------------------------
7
我有一个表：
名称       数量
TT           11
BB           12
TT           13
DD           14
BB           15

得出来的结果是
TT          24
BB          27
TT          24
DD          14
BB          27

答
SELECT 名称,base.数量
     FROM [table]
     LEFT JOIN
     (
           SELECT 名称,SUM(数量) AS 数量 FROM [table] GROUP BY 名称
     ) base
     ON base.名称=[table].名称
8
首先有现有的数据表A：
AA1      AA2       AA3
010      北京      5
021      上海      3
022      天津      4
023      重庆      2
。。。。
想得到另一个表B，其内容与A类似，只是没有最后一列，并且如下：
AA1      AA2
010      北京
010      北京
010      北京
010      北京
010      北京
021      上海
021      上海
021      上海
022      天津
022      天津
022      天津
022      天津
023      重庆
023      重庆
。。。。
即所得到的B表，相应记录的内容与A相同，但B表中记录的行数取决于A表中AA3的数值。

答
----生成临时数据
DECLARE @A Table
(AA1Char(3),
 AA2Nvarchar(10),
 AA3Int)
Insert @A Select '010',      N'北京',      5
Union All Select '021',      N'上海',      3
Union All Select '022',      N'天津',      4
Union All Select '023',      N'重庆',      2

----定义递规查询变量
DECLARE @City VARCHAR(8000)
SET @City=''

SELECT @City=@City + REPLICATE(AA2 + ',',AA3) FROM @A
SELECT @City=LEFT(@City,LEN(@City)-1)

----生成结果数据
SELECT AA1,a FROM dbo.Split(@City,',') INNER JOIN @A ON AA2=a


----附Split函数
CREATE Function Split(@Sql varchar(8000),@Splits varchar(10))
returns @temp Table (a varchar(100))
As
Begin
Declare @i Int
Set @Sql = RTrim(LTrim(@Sql))
Set @i = CharIndex(@Splits,@Sql)
While @i >= 1
Begin
Insert @temp Values(Left(@Sql,@i-1))
Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i)
Set @i = CharIndex(@Splits,@Sql)
End

If @Sql <> ''
Insert @temp Values (@Sql)
Return
End
-------------------------------------------------------------------------

9
本人要sql后台写一个单据的存储过程,其中有一段是求两个数相除的绝对值,代码如下abs(((select isnull(camount,0) from #tmp_B where id=@i-1)-isnull(jamount,0)+isnull(damount,0))/((select isnull(cquantity ,0) from #tmp_B where id=@i-1)-isnull(jquantity,0)+isnull(dquantity,0)))。这个表达式有一种可能时不成立，哪就是当除数是0时，请教各位当((select isnull(cquantity ,0) from #tmp_B where id=@i-1)-isnull(jquantity,0)+isnull(dquantity,0))为0即除数为0时，如何将这个除数变为1。
答
不太喜欢用CASE WHEN,某些时候用函数更有效.
将某个变量为特定值时指定为其它值的写法:
比如当a 为0时要求a为1,其它时候为他本身.那么
DECLARE @a INT
SET @a=1
SELECT ISNULL(NULLIF(@a,0),1)
-----------------------------------------------------------

10
对一个已经存在的表格操作：

假设一个表是3行3列，先按列乘 再按行加

例如
table
sm1  sm2  sm3 sm4  sm5
 1    2    3   3    3  
 3    3    6   5    4
 4    4    5   2    2
 3    3    6   4    2
要求的到一下结果：

1*2*3*3*3+3*3*6*5*4+4*4*5*2*2+3*3*6*4*2
答
create table tb(sm1 int,sm2 int,sm3 int, sm4 int,sm5 int)
insert tb
select 1,2,3,3,3  union all
select 3,3,6,5,4  union all
select 4,4,5,2,2  union all
select 3,3,6,4,2
declare @s varchar(1024)
set @s=''
select @s=@s+rtrim(cast(sm1 as char(10))) + '*' + rtrim(cast(sm2 as char(10))) + '*' + rtrim(cast(sm3 as char(10))) + '*' + rtrim(cast(sm4 as char(10))) + '*' + rtrim(cast(sm5 as char(10))) from tb
print @s
----------------------------------------------------------------


11
select * from ad_hits where ad_id = '131' and year(hits_date) = '2006' and
month(hits_date) = '6' order by hits_date
======================================================
用上面的搜索语句,得到下面的结果..
1312006-06-01 00:00:001
1312006-06-02 00:00:002
1312006-06-03 00:00:001
1312006-06-05 00:00:002
1312006-06-06 00:00:005
1312006-06-07 00:00:001
1312006-06-10 00:00:001
1312006-06-14 00:00:002
==================================
我想得到这样的结果..
1312006-06-01 00:00:001
1312006-06-02 00:00:002
1312006-06-03 00:00:001
1312006-06-04 00:00:000
1312006-06-05 00:00:002
1312006-06-06 00:00:005
1312006-06-07 00:00:001
1312006-06-08 00:00:000
1312006-06-09 00:00:000
1312006-06-10 00:00:001
1312006-06-11 00:00:000
1312006-06-12 00:00:000
1312006-06-13 00:00:000
1312006-06-14 00:00:002

就是,如果当天的点击数为O则自动列出为0记录...

答
--生成测试数据
DECLARE @tb TABLE(ID INT,YourDate DATETIME,NUM INT)
INSERT @tb
SELECT
131,'2006-06-01 00:00:00',1
UNION SELECT
131,'2006-06-02 00:00:00',2
UNION SELECT
131,'2006-06-03 00:00:00',1
UNION SELECT
131,'2006-06-05 00:00:00',2
UNION SELECT
131,'2006-06-06 00:00:00',5
UNION SELECT
131,'2006-06-07 00:00:00',1
UNION SELECT
131,'2006-06-10 00:00:00',1
UNION SELECT
131,'2006-06-14 00:00:00',2


--生成临时数据
DECLARE @MinDate Datetime,@MaxDate Datetime
SELECT @MinDate=MIN(YourDate),@MaxDate=MAX(YourDate) FROM @tb
DECLARE @Tmp Table(Num1 INT,TmpDate DateTime,Num2 INT)
WHILE @MinDate<@MaxDate
BEGIN
INSERT @Tmp VALUES(131,DATEADD(dd,1,@MinDate),0)
SET @MinDate=DATEADD(dd,1,@MinDate)
END

--显示所需结果
SELECT * FROM @tb
UNION
SELECT * FROM @Tmp WHERE TmpDate NOT IN(SELECT YourDate FROM @tb)
-------------------------------------------------------------------------
12
有个字段存放的是访问URL的记录，有url和参数，象这样
http://www.163.com/news/sports.asp?title=aaa&author=bbb
现在我想把它拆成url，参数和参数值对，该怎么弄呢？
就这样：
http://www.163.com/
/news/sports.asp
title aaa
author bbb
答
DECLARE @s VARCHAR(1024),@Domain CHAR(100),@Path CHAR(100),@UrlWithoutPm CHAR(200),@Pm CHAR(200)
SET @s='http://www.163.com/news/sports.asp?title=aaa&author=bbb'
SET @UrlWithoutPm=LEFT(@s,CHARINDEX('?',@s)-1)
SET @Pm=REPLACE(@s,RTRIM(@UrlWithoutPm)+'?','') + '&x'
SET @Domain=LEFT(@UrlWithoutPm,CHARINDEX('/',REPLACE(@UrlWithoutPm,'http://',''))+6)
SET @Path=REPLACE(@UrlWithoutPm,@Domain,'')
DECLARE @tb TABLE(aaa CHAR(100))

WHILE CHARINDEX('&',@Pm)>0
BEGIN
INSERT @tb SELECT REPLACE(LEFT(@Pm,CHARINDEX('&',@Pm)-1),'=',' ')
SET @Pm=SUBSTRING(@Pm,CHARINDEX('&',@Pm)+1,LEN(@Pm))

END

SELECT @Domain
SELECT @Path
SELECT aaa FROM @tb



--写成函数的的话
CREATE FUNCTION fn_SplitURL(@s VARCHAR(1024))
RETURNS @spu TABLE(item CHAR(200))
AS
BEGIN
DECLARE @Domain CHAR(100),@Path CHAR(100),@UrlWithoutPm CHAR(200),@Pm CHAR(200)
SET @UrlWithoutPm=LEFT(@s,CHARINDEX('?',@s)-1)
SET @Pm=REPLACE(@s,RTRIM(@UrlWithoutPm)+'?','') + '&x'
SET @Domain=LEFT(@UrlWithoutPm,CHARINDEX('/',REPLACE(@UrlWithoutPm,'http://',''))+6)
SET @Path=REPLACE(@UrlWithoutPm,@Domain,'')
DECLARE @tb TABLE(aaa CHAR(100))

WHILE CHARINDEX('&',@Pm)>0
BEGIN
INSERT @tb SELECT REPLACE(LEFT(@Pm,CHARINDEX('&',@Pm)-1),'=',' ')
SET @Pm=SUBSTRING(@Pm,CHARINDEX('&',@Pm)+1,LEN(@Pm))

END
INSERT @spu
SELECT @Domain
UNION
SELECT @Path
UNION
SELECT aaa FROM @tb
RETURN
END

SELECT * FROM dbo.fn_SplitURL('http://www.sina.com/aa/bb.asp?ddd=1&ccc=2')

13
MS SQL server2000数据库，表中有一个字段，类型为：nvarchar　，实际上里面存储的应该全都是数字记录，但是数据库建立较早，早期有一些非数字记录（如汉字 半,一 等）保存进去了！

因为现在要根据这个字段进行排序，因为是nvarchar型的，所以排序的时候很乱，比如按降序排列时，明明20比3大，但排序的时候3会排到20的前面，可能是因类字段类型的问题，于是想将其类型改为int型，但是里面包含一些非数字记录，更改时出错！

可不可以用一条SQL语句将非数字记录全部更新成为一个数字（1或2），或是干脆删除掉这些记录？
不过Isnumeric有点问题，会漏掉一些数据
比如2d,2e等等
答
declare @t table(id nvarchar(10))
insert @t select N'123'
union all select N'234'
union all select N'a3'
union all select N'ta3'
union all select N'2d'
union all select N'2e'
select * from @t

select * from @t where id like '[0-9][de]' or isnumeric(id)=1

将不是数字的更新为1的写法是
update @t set id=1 where not (id like '[0-9][de]' or isnumeric(id)=1)
或者
update @t set id=1 where not id like '[0-9][de]' and not isnumeric(id)=1
-------------------------------------------------------------------

14
请哪位高手提供一段将某个字段中所有记录的地名删除掉的代码,我单位数据库中需要将"企业名称"字段中抬头的地区名删除掉,就是比如"苏州托普信息技术学院"要将"苏州"两字去掉,将所有包含"苏州"或"苏州市"或"江苏省苏州市"或"江苏省苏州"的字段中的这些字删除掉,应该如何做啊?请给出具体代码啊,谢谢!

答
--源数据表
DECLARE @tbInfo TABLE(ID INT,Info CHAR(100))
INSERT @tbInfo
SELECT 1,'苏州大学'
UNION
SELECT 2,'上海大学'
UNION
SELECT 3,'上海艺术大学'

--创建一个地名数据表
DECLARE @tbCity TABLE(City CHAR(20))
INSERT @tbCity
SELECT '苏州'
UNION SELECT '上海'
UNION SELECT '北京'


--去掉相关地名
UPDATE @tbInfo SET Info=REPLACE(Info,Bcity,'') FROM (SELECT Info Binfo ,ID,City BCity FROM @tbInfo
INNER JOIN @tbCity
ON CHARINDEX(RTRIM(City),Info)>0) B WHERE Info=Binfo

--显示去掉后的数据
SELECT * FROM @tbInfo

-----------------------------------------------------------------------
15
数据库中的字段keywords存储的字符串是用逗号分割开的，如下
ID    keywords
1     beijing,xian,shanghai
2     xian,shenzhen
3     beijing，xian
输入字符串如 str="beijing travel tours..."
如何用SQL语句实现查找出keywords中用逗号分割开的各字符串存在输入字符串中的记录
以上就应该查出ID=1，3的记录
望各位帮帮忙，谢谢了

--生成测试数据
DEClARE @tb TABLE(ID INT,Keywords CHAR(200))
INSERT @tb
SELECT
1,     'beijing,xian,shanghai'
UNION SELECT
2,     'xian,shenzhen'
UNION SELECT
3,     'beijing，xian'

--待搜索字串
DECLARE @s CHAR(200)
SET @s='beijing travel'

--显示搜索结果
SELECT ID,Keywords FROM @tb
LEFT JOIN
(SELECT ',' + a + ',' k FROM dbo.Split(@s,' ')) b
ON CHARINDEX(k,','+Keywords+',')>0 WHERE ISNULL(k,'')<>''

附Split函数

CREATE Function Split(@Sql varchar(8000),@Splits varchar(10))
returns @temp Table (a varchar(100))
As
Begin
Declare @i Int
Set @Sql = RTrim(LTrim(@Sql))
Set @i = CharIndex(@Splits,@Sql)
While @i >= 1
Begin
Insert @temp Values(Left(@Sql,@i-1))
Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i)
Set @i = CharIndex(@Splits,@Sql)
End

If @Sql <> ''
Insert @temp Values (@Sql)
Return
End

--------------------------------------------------------------------
16
有表t1  表t1里面有UID字段，并且是可重复的
T1
uid   count
001   32.7
002   45.2
001   44.0
002   84.6
009   75.4
005   74.2
要求是　用一条语语实实现　uid等于　001,002,005的字段count的各个总和
答
SELECT uid,SUM([count]) FROM [table] WHERE uid IN('001,002,005') GROUP BY uid
----------------------------------------------------------------------

17
大家好!
    我有一个数据库表catalog,有两个重要字段ID,parentID(parentID为ID对应的父类),这个类别可以无限级的分下去.
    现在有个问题比较困惑,我想写一个用户定义函数GetAllCataID( cataID int ),想返回一个指定ID下的所有子类,不知怎么写?
    参于有分,答对有高分!
    在线急等.......
答
DECLARE @tb TABLE(id INT,parentid INT)
INSERT @tb
SELECT 1,0
UNION
SELECT 2,1
UNION
SELECT 3,2
UNION SELECT
4,0
UNION SELECT
5,4
DECLARE @path VARCHAR(1024),@curid INT
SET @curid=1  --你要查询的根节点
SET @path=''
WHILE EXISTS(SELECT * FROM @tb WHERE parentid=@curid)
     BEGIN
     SELECT @path=@path + ' ' + RTRIM(id),@curid=id FROM @tb WHERE parentid=@curid
     END
PRINT @path
-----------------------------------------
18
declare @columname char(10)
set @columname=convert(char(10),getdate(),20)
exec('alter table abcd
add ['@columname'] int')

请问以上语句哪儿有问题啊？
一运行就说“第４行：‘@columname’附近有语法错误”
但我实在不知到错在哪儿了，请高手指点一下！
答
楼主的写法错误是因为:

declare @columname char(10)
set @columname=convert(char(10),getdate(),20)
exec('alter table abcd
add ['@columname'] int')

@columname是在这里声明的,而编译动态SQL语句时,因为主代码与 exec中所执行的代码级别不同,编译不在同一空间,对于exec中的那段代码来说@columname是没有声明过的变量.
19
有表A，B
A:
id     qty
1       12
2       13
3       14
4       15
5       16

B:
id      dept      qty
1      a      11
1      b      12
1       b       12
2      a      13
4      c      14


求结果：
id     qty      dept_A_qty      detp_B_qty      dept_C_qty
1       12      11            24(12+12)      0
2       13      13            0            0
3       14      0            0            0
4       15      0            0            14
5       16      0            0            0

其中表B中的dept可能的会很多，有n种类型，就要在结果中多添加n个字段

答
create table A(id int,qty int)
insert a select 1,12
union all select 2,13
union all select 3,14
union all select 4,15
union all select 5,16

create table B(id int,dept varchar(20),qty int)
insert b
select 1,'a',11
union all select 1,'b',12
union all select 1,'b',12
union all select 2,'a',13
union all select 4,'c',14


DECLARE @f VARCHAR(1024),@f1 VARCHAR(1024)
SET @f=''
SET @f1=''

SELECT @f=@f + ',dept_' + dept + '_qty=(SELECT SUM(s.qty) FROM B s WHERE s.dept=b.dept and s.dept=''' + dept + ''' and s.id=a.id)' FROM B GROUP BY dept
SELECT @f1=@f1 + ',ISNULL(MAX(dept_' + dept +'_qty),0)' FROM B  GROUP BY dept
SELECT @f='SELECT a.id id,a.qty qty' + @f + ' FROM A a LEFT JOIN B b ON a.id=b.id'
SELECT @f='SELECT id,qty' + RTRIM(@f1) + 'FROM (' + @f + ') base GROUP BY id,qty'


EXEC (@f)

DROP TABLE A
DROP TABLE B

result:

1      12      11      24      0
2      13      13      0      0
3      14      0      0      0
4      15      0      0      14
5      16      0      0      0
在计算值时用CASE WHEN写法会简便很多，我是直写的，有点烦琐，另外效率上也不是很高，楼主自行改一下吧
--------------------------------------------------------------------------------------

20
现有如下表结构
 ------------------
|userid    roleid |
|------------------
|38      14|      |
|211      64|48|   |
|212      14|      |
|213      14|      |
|214      14|      |
|215      14|      |
|224      14|      |
-------------------
要求把64|48| 变成2条记录，其他的保留，如下
 ------------------
|userid    roleid |
|------------------
|38      14|      |
|211      64|      |
|211     48|      |
|212      14|      |
|213      14|      |
|214      14|      |
|215      14|      |
|224      14|      |
-------------------
前面可以加一列ID，自动编号
谢谢了！

答
CREATE TABLE u (userid int,roleid char(100))
INSERT u (userid,roleid)
SELECT           38,      '14|      |'
UNION SELECT 211,      '64|48|   |'
UNION SELECT 212,      '14|      |'
UNION SELECT 213,      '14|      |'
UNION SELECT 214,      '14|      |'
UNION SELECT 215,      '14|      |'
UNION SELECT 224,      '14|      |'



DECLARE @r VARCHAR(1024)
SET @r=''
SELECT @r=RTRIM(@r) + RTRIM(roleid)FROM u


SELECT a.userid,b.roleid,IDENTITY(INT) NewID INTO #TMP FROM u a
INNER JOIN
     (SELECT tempColumn as roleid FROM dbo.Split(@r,'|') WHERE RTRIM(tempColumn)<>'') b
     ON CHARINDEX('|'+b.roleid + '|','|'+a.roleid)>0 GROUP BY a.userid,b.roleid
TRUNCATE TABLE u
--ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
ALTER TABLE u ADD NewID INT NULL
INSERT u SELECT * FROM #tmp
SELECT * FROM u
DROP TABLE #tmp

DROP TABLE u

附split函數
CREATE Function Split(@Sql varchar(8000),@Splits varchar(100))
returns @temp Table (tempColumn varchar(100))
As
Begin
Declare @i Int
Set @Sql = RTrim(LTrim(@Sql))
Set @i = CharIndex(@Splits,@Sql)
While @i >= 1
Begin
Insert @temp Values(Left(@Sql,@i-1))
Set @Sql = SubString(@Sql,@i+len(@Splits),Len(@Sql)-@i)
Set @i = CharIndex(@Splits,@Sql)
End
If @Sql <> ''
Insert @temp Values (@Sql)
Return
End
------------------------------------------------------------