数据库MYSQL5.1 创建数据库 create database oasystem 建表 create table file_info ( id int(11), upload_men varchar(50), uploadtime varchar(50), branch varchar(50) ); 查询upload_men 、branch 、uploadtime 这三个字段如果都有值的话 查询结果类似 select * from file_info where upload_men='' AND branch='' AND uploadtime>='2010-10-1' AND uploadtime<='2010-12-1' 如果 其中两字段 有值的话 查询结果类似下面(这里取 upload_men 、uploadtime。 ) 要求 branch 对查询结果没有任何影响select * from file_info where upload_men='' AND uploadtime>='2010-10-1' AND uploadtime<='2010-12-1' 怎么用一句SQL语句实现
SELECT * from file_info WHERE (uploadtime >= '2010-12-17' AND uploadtime <= '2010-12-22' AND branch='' AND upload_men='张三') or (uploadtime >= '2010-12-17' AND uploadtime <= '2010-12-22' AND branch='' ) or (uploadtime >= '2010-12-17' AND uploadtime <= '2010-12-22' AND upload_men='张三' ) or ( branch='' AND upload_men='张三') or (uploadtime >= '2010-12-17' AND uploadtime <= '2010-12-22' ) or (upload_men='张三') or ( branch='' )写的不对
下面就是你提供的测试数据?! 有自己试过吗?mysql> create table file_info -> ( -> id int(11), -> upload_men varchar(50), -> uploadtime varchar(50), -> branch varchar(50), -> premission varchar(50), -> -> -> ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 10mysql> insert into file_info ( upload_men,uploadtime,branch,premission) values ('张三','2010-12-18','地球','asdfasdfasdf'); Query OK, 1 row affected (0.03 sec)mysql> insert into file_info ( upload_men,uploadtime,branch,premission) values ('李四','2010-12-19','火星'.'asdfasdf'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.'asd fasdf')' at line 1
查询条件是提供对应三个字段至少一个的值 与数据库upload_men,uploadtime,branch这些字段对应匹配mysql> select * from file_info where upload_men='张三' or uploadtime='张三' or b ranch='张三'; +------+------------+------------+--------+--------------+ | id | upload_men | uploadtime | branch | premission | +------+------------+------------+--------+--------------+ | NULL | 张三 | 2010-12-18 | 地球 | asdfasdfasdf | +------+------------+------------+--------+--------------+ 1 row in set (0.03 sec)mysql>
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
create table file_info
(
id int(11),
upload_men varchar(50),
uploadtime varchar(50),
branch varchar(50)
);
查询upload_men 、branch 、uploadtime
这三个字段如果都有值的话 查询结果类似 select * from file_info where upload_men='' AND branch='' AND uploadtime>='2010-10-1' AND
uploadtime<='2010-12-1'
如果 其中两字段 有值的话 查询结果类似下面(这里取 upload_men 、uploadtime。 )
要求 branch 对查询结果没有任何影响select * from file_info where upload_men='' AND uploadtime>='2010-10-1' AND uploadtime<='2010-12-1'
怎么用一句SQL语句实现
SELECT * from file_info
WHERE
(uploadtime >= '2010-12-17' AND uploadtime <= '2010-12-22' AND branch='' AND upload_men='张三')
or
(uploadtime >= '2010-12-17' AND uploadtime <= '2010-12-22' AND branch='' )
or
(uploadtime >= '2010-12-17' AND uploadtime <= '2010-12-22' AND upload_men='张三' )
or
( branch='' AND upload_men='张三')
or
(uploadtime >= '2010-12-17' AND uploadtime <= '2010-12-22' )
or
(upload_men='张三')
or
( branch='' )写的不对
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
create table file_info
(
id int(11),
upload_men varchar(50),
uploadtime varchar(50),
branch varchar(50),
premission varchar(50),
);
insert into file_info ( upload_men,uploadtime,branch,premission) values('张三','2010-12-18','地球','asdfasdfasdf');
insert into file_info ( upload_men,uploadtime,branch,premission) values('李四','2010-12-19','火星'.'asdfasdf');查询条件是提供对应三个字段至少一个的值
与数据库upload_men,uploadtime,branch这些字段对应匹配
结果是 对应记录的所有信息就是类似这样一个高级搜索功能
http://pengyou.qq.com/index.php?mod=search&act=advanced
-> (
-> id int(11),
-> upload_men varchar(50),
-> uploadtime varchar(50),
-> branch varchar(50),
-> premission varchar(50),
->
->
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
line 10mysql> insert into file_info ( upload_men,uploadtime,branch,premission) values
('张三','2010-12-18','地球','asdfasdfasdf');
Query OK, 1 row affected (0.03 sec)mysql> insert into file_info ( upload_men,uploadtime,branch,premission) values
('李四','2010-12-19','火星'.'asdfasdf');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '.'asd
fasdf')' at line 1
与数据库upload_men,uploadtime,branch这些字段对应匹配mysql> select * from file_info where upload_men='张三' or uploadtime='张三' or b
ranch='张三';
+------+------------+------------+--------+--------------+
| id | upload_men | uploadtime | branch | premission |
+------+------------+------------+--------+--------------+
| NULL | 张三 | 2010-12-18 | 地球 | asdfasdfasdf |
+------+------------+------------+--------+--------------+
1 row in set (0.03 sec)mysql>