投票类型相关sql语句的写法

1.建表

game_shoevote

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[game_shoevote]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[game_shoevote]
GO

CREATE TABLE [dbo].[game_shoevote] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [number] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [allcount] [int] NULL ,
 [flag] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[game_shoevote] ADD
 CONSTRAINT [DF_game_shoevote_allcount] DEFAULT (0) FOR [allcount],
 CONSTRAINT [DF_game_shoevote_flag] DEFAULT (1) FOR [flag],
 CONSTRAINT [PK_game_shoevote] PRIMARY KEY  CLUSTERED
 (
  [id]
 )  ON [PRIMARY]
GO

2.//初始化数据,从1到27
declare @num int
set @num=1
while @num<=27
begin
   insert into game_shoevote(number,allcount,flag) values(@num,0,1)
   set @num=@num+1
end

3.//对客户上行的候选号进行处理

/*

*客户提交的信息进行处理,如:10+01+2+02
*转化为:10,1,2,2结果集返回
*/
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(num varchar(100))
--实现split功能 的函数
--date :2005-4-20
--Author :Domino
--date :2009-03-30
--Modify Autho:fjfdszj
as
begin
declare @i int     /*控制循环次数*/
declare @value int /*去零方法*/
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
set @value=0;
while @i>=1
begin
 if(ISNUMERIC(left(@SourceSql,@i-1))=1)/*去零方法*/
            begin
      set @value=left(@SourceSql,@i-1)
      insert @temp values(@value)
            end
 else /*字符串*/
     insert @temp values(left(@SourceSql,@i-1))

set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'/'
        begin
  if(ISNUMERIC(@SourceSql)=1)/*去零方法*/
                begin
   set @value=@SourceSql
   insert @temp values(@value)
                end
  else /*字符串*/
       insert @temp values(@SourceSql)
        end
return
end
4.可用以上函数处理如下问题

A.客户上行候选号个数

select count(*) from db3Tsms.dbo.f_split(@curcontent,'+');

B.客户上行候选号重复

select @flag=count(*) from 
(
   select count(num) num from db3Tsms.dbo.f_split(@curcontent,'+') group by num having count(num)>1
 )aa

C.客户上行的候选号不存在于基本表中

select @flag=count(*) from db3Tsms.dbo.f_split(@curcontent,'+') where not EXISTS
       (select * from db3Tsms.dbo.game_shoevote where num=number )