单表多条件查询sql语句的问题

[philchang718]

T_USER表中有
[USER_ID] [int] IDENTITY(1,1) NOT NULL,
[USER_LOGIN_NAME] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[USER_REAL_NAME] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[USER_NICK_NAME] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[USER_PASSWORD] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[SEX] [bit] NOT NULL,
[COMMENT] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[BIRTHDAY] [datetime] NULL,
[COUNTRY] [int] NOT NULL,
[PROVINCE] [int] NOT NULL
这些列，现在我要在页面上设置[USER_LOGIN_NAME]；[USER_NICK_NAME] ；[BIRTHDAY] 为条件进行查询，当条件全部为空时，查询全部，当其中一个或两个条件为空时，为空的条件不参与查询，这样的话sql怎么写？谢谢。

[cylot]

if(USER_LOGIN_NAME==""&&USER_NICK_NAME==""&&BIRTHDAY=="")
sqlText="Select * from T_USER";
else if(USER_LOGIN_NAME!="")
sqlText="Select * from T_USER where USER_LOGIN_NAME="+USER_LOGIN_NAME;
else if(USER_NICK_NAME)
sqlText+=" and USER_NICK_NAME="+USER_NICK_NAME;
else if(BIRTHDAY!="")
sqlText+=" and BIRTHDAY="+BIRTHDAY;

[changjiangzhibin]

private string SearchSql(string loginName,string nickName,string birthday)
{
  string sql = "select * from t_user";
  bool hasSearch = false;
  if(loginName.Length > 0)
  {
    hasSearch = true;
    sql += " where USER_LOGIN_NAME = '"+loginName+"'";
  }
  if(nickName.Length > 0)
  {
    if(hasSearch)
    {
      sql += " and USER_NICK_NAME = '"+nickName+"'";
    }
    else
    {
      sql += " where USER_NICK_NAME = '"+nickName+"'";
      hasSearch = true;
    }
  }
  if(birthday.Length > 0)
  {
    if(hasSearch)
    {
      sql += " and BIRTHDAY='"+birthday+"'";
    }
    else
    {
      sql += " where BIRTHDAY='"+birthday+"'";
    }
  }
} 

[zhangqingfu]

给你个简单而实用的：
SELECT *
FROM t_user
WHERE ISNULL(@loginName, USER_LOGIN_NAME) = USER_LOGIN_NAME
AND ISNULL(@nickName, USER_NICK_NAME) = USER_NICK_NAME
AND ISNULL(@birthday, BIRTHDAY) = BIRTHDAY

[wolf_410]

string sqlstr="";
if(USER_LOGIN_NAME!=""&&USER_LOGIN_NAME!=null)
    sqlstr+=" and USER_LOGIN_NAME='"+USER_LOGIN_NAME+"'";
if(USER_NICK_NAME!=""&&USER_NICK_NAME!=null)
    sqlstr+=" and USER_NICK_NAME='"+USER_NICK_NAME+"'";
if(BIRTHDAY!=""&&BIRTHDAY!=null)
    sqlstr+=" and BIRTHDAY='"+BIRTHDAY+"'";
sqlstr="select * from t_user where 1=1"+sqlstr;
   

[conan304]

create procedure proc_find
@user_login_name varchar(50)=null,
@user_nick_name varchar(50)=null
@birthday varchar(50)=null
AS

select * from T_USER
where (@user_login_name is null or user_login_name=@user_login_name)
	and (@user_nick_name is null or user_nick_name=@user_nick_name)
	and (@birthday is null or birthday=@birthday)

[philchang718]

能直接写sql语句吗？

[UltraBejing]

这个简单啊,网上搜一下就得到答案了.

[knowledge_Is_Life]

以后需再关注，现在先帮你顶一下