有张表,里面有几个字段
如:
userid birth city
00001 19820101 福州
00002 19820501 泉州
00011 19820101 福州
00021 19820801 厦门
00041 19820101 福州
00701 19820801 厦门
01201 19820501 福州
…………
我现在想
输入用户ID(userid)
输出如下
如果有同年同月同日的输出
找不到就找同月同日同城市的
找不到就找同月同日不同城市的
我的是这样写的
CREATE proc bb
@userid varchar(21)
as
if (@userid is null )
begin
raiserror ('nullvalue are invalid',5,5)
return
end
select a.cell_no,a.nickname,a.sex,a.userid,a.xz,a.birth,
b.cell_no,b.nickname,b.sex,b.userid
from pd_users a,pd_users b
where
a.cell_no=@usernumber and a.birth)=b.birth and a.sex<>b.sex
select a.cell_no,a.nickname,a.sex,a.userid,a.xz,a.birth,
b.cell_no,b.nickname,b.sex,b.userid
from pd_users a,pd_users b
where
a.cell_no=@usernumber and datepart(mm,a.birth)=datepart(mm,b.birth) and a.sex<>b.sex and a.city=b.cityselect a.cell_no,a.nickname,a.sex,a.userid,a.xz,a.birth,
b.cell_no,b.nickname,b.sex,b.userid
from pd_users a,pd_users b
where
a.cell_no=@usernumber and datepart(mm,a.birth)=datepart(mm,b.birth) and a.sex<>b.sex
exec bb
@usernumber='userid'
可是老提示出错啊
要怎么写
帮帮忙
还有,我要把找到的用户存到表 aa 中
如:
userid birth city
00001 19820101 福州
00002 19820501 泉州
00011 19820101 福州
00021 19820801 厦门
00041 19820101 福州
00701 19820801 厦门
01201 19820501 福州
…………
我现在想
输入用户ID(userid)
输出如下
如果有同年同月同日的输出
找不到就找同月同日同城市的
找不到就找同月同日不同城市的
我的是这样写的
CREATE proc bb
@userid varchar(21)
as
if (@userid is null )
begin
raiserror ('nullvalue are invalid',5,5)
return
end
select a.cell_no,a.nickname,a.sex,a.userid,a.xz,a.birth,
b.cell_no,b.nickname,b.sex,b.userid
from pd_users a,pd_users b
where
a.cell_no=@usernumber and a.birth)=b.birth and a.sex<>b.sex
select a.cell_no,a.nickname,a.sex,a.userid,a.xz,a.birth,
b.cell_no,b.nickname,b.sex,b.userid
from pd_users a,pd_users b
where
a.cell_no=@usernumber and datepart(mm,a.birth)=datepart(mm,b.birth) and a.sex<>b.sex and a.city=b.cityselect a.cell_no,a.nickname,a.sex,a.userid,a.xz,a.birth,
b.cell_no,b.nickname,b.sex,b.userid
from pd_users a,pd_users b
where
a.cell_no=@usernumber and datepart(mm,a.birth)=datepart(mm,b.birth) and a.sex<>b.sex
exec bb
@usernumber='userid'
可是老提示出错啊
要怎么写
帮帮忙
还有,我要把找到的用户存到表 aa 中
@userid varchar(21)
asif (@userid is null )
begin
raiserror ('nullvalue are invalid',5,5)
return
endselect a.*
from pd_users a join pd_users b on a.birth=b.birth
where b.userid=@userid and a.userid<>@userid
if @@rowcount=0
begin
select a.*
from pd_users a join pd_users b on a.city=b.city
and right(a.birth,4)=right(b.birth,4)
where b.userid=@userid and a.userid<>@userid
if @@rowcount=0
begin
select a.*
from pd_users a join pd_users b on right(a.birth,4)=right(b.birth,4)
where b.userid=@userid and a.userid<>@userid
end
end
CREATE proc bb
@userid varchar(21)
as
if (@userid is null )
begin
raiserror ('nullvalue are invalid',5,5)
return
end--如果有同年同月同日的输出
select a.cell_no,a.nickname,a.sex,a.userid,a.xz,a.birth,
b.cell_no,b.nickname,b.sex,b.userid
from pd_users a inner join pd_users b on a.cell_no=b.cell_no
where
a.cell_no=@usernumber and a.birth=b.birth and a.sex<>b.sex
if @@rowcount>0 return--找同月同日同城市的
select a.cell_no,a.nickname,a.sex,a.userid,a.xz,a.birth,
b.cell_no,b.nickname,b.sex,b.userid
from pd_users a inner join pd_users b on a.cell_no=b.cell_no
where
a.cell_no=@usernumber and datepart(mm,a.birth)=datepart(mm,b.birth) and a.sex<>b.sex and a.city=b.city
if @@rowcount>0 return--找同月同日不同城市的
select a.cell_no,a.nickname,a.sex,a.userid,a.xz,a.birth,
b.cell_no,b.nickname,b.sex,b.userid
from pd_users a inner join pd_users b on a.cell_no=b.cell_no
where
a.cell_no=@usernumber and datepart(mm,a.birth)=datepart(mm,b.birth) and a.sex<>b.sex
---------------------------insert into aa (userid ) select a.userid from ......
victorycyz(中海)
在插入时出现错误
我只想把找到的用户ID(userid)和生日 放到AA中
表aa
userid birth
你能不能具体点,要这句要放到哪里??
麻烦你了
但是错现了一种就是
如果,是同月同日不同城市的
出现这样
例如
------------------------------------------------
无效列名 userid birth city 无效列名 userid birth city
------------------------------------------------
无效列名 userid birth city 无效列名 userid birth city
-----------------------------------------
无效列名 userid birth city 无效列名 userid birth city
同月同日不同城市 00001 19820101 福州 00041 19820101 福州
问下,能不能不让前面两列出现呢??
@userid varchar(21)
as
if (@userid is null )
begin
raiserror ('nullvalue are invalid',5,5)
return
end--如果有同年同月同日的输出
select a.cell_no,a.nickname,a.sex,a.userid,a.xz,a.birth,
b.cell_no,b.nickname,b.sex,b.userid
from pd_users a inner join pd_users b on a.cell_no=b.cell_no
where
a.cell_no=@usernumber and a.birth=b.birth and a.sex<>b.sex
if @@rowcount>0 return--找同月同日同城市的
select a.cell_no,a.nickname,a.sex,a.userid,a.xz,a.birth,
b.cell_no,b.nickname,b.sex,b.userid
from pd_users a inner join pd_users b on a.cell_no=b.cell_no
where
a.cell_no=@usernumber and datepart(mm,a.birth)=datepart(mm,b.birth) and a.sex<>b.sex and a.city=b.city
if @@rowcount>0 return--找同月同日不同城市的
select a.cell_no,a.nickname,a.sex,a.userid,a.xz,a.birth,
b.cell_no,b.nickname,b.sex,b.userid
from pd_users a inner join pd_users b on a.cell_no=b.cell_no
where
a.cell_no=@usernumber and datepart(mm,a.birth)=datepart(mm,b.birth) and a.sex<>b.sex
这就没有问题啊!!