当前位置:Gxlcms > PHP教程 > 求大神解答一个数据库语句的问题

求大神解答一个数据库语句的问题

时间:2021-07-01 10:21:17 帮助过:16人阅读

SELECT `id` , `time` , `title`FROM `think_infor`WHERE `type` LIKE '$k-%'			UNION ALL			SELECT `id` , `time` , `title`FROM `think_infor2`WHERE `type` LIKE '$k-%'			ORDER BY `time` DESCLIMIT 0,6


用这个语句可以查询到表1和表2的最新6条信息。但是怎么判断记录是哪个表返回的?
或者有什么其他的方法可以实现,不想多次查询


回复讨论(解决方案)

你带上表名不就行了?

不过你能得到 表1和表2的最新6条信息 吗?

SELECT A.id AS aid , A.time AS atime , A.title AS atitle, B.id AS bid , B.time AS btime , B.title AS btitle
FROM think_infor AS A, think_infor2 AS B
WHERE A.type LIKE '$k-%' AND B.type=A.type
ORDER BY A.time DESC LIMIT 0,6

SELECT A.id AS aid , A.time AS atime , A.title AS atitle, B.id AS bid , B.time AS btime , B.title AS btitle
FROM think_infor AS A, think_infor2 AS B
WHERE A.type LIKE '$k-%' AND B.type=A.type
ORDER……


这个差不多,但是A表的记录是会有重复记录

SELECT `id` , `time` , `title`, 1 as cpfromFROM `think_infor`WHERE `type` LIKE '$k-%'			UNION ALL			SELECT `id` , `time` , `title` , 2 as cpfromFROM `think_infor2`WHERE `type` LIKE '$k-%'			ORDER BY `time` DESCLIMIT 0,6

这个语句写的不是很清楚

引用 2 楼 yiwusuo 的回复:SELECT A.id AS aid , A.time AS atime , A.title AS atitle, B.id AS bid , B.time AS btime , B.title AS btitle
FROM think_infor AS A, think_infor2 AS B
WHERE A.type LIKE……
ORDER BY A.time DESC 后面加上:having count(A.id)=1
不就没重复了吗

我自己都觉得乱了,我还是说一下我想要的东西吧


有A表,B表,结构相同,数据不同,我想同时模糊查询一个字段`type`,选取两表中符合条件的前6条记录(根据`time`排序)

你自己写的不就可以吗?

SELECT `id` , `time` , `title`, 'think_infor' as tbl_name  FROM `think_infor`  WHERE `type` LIKE '$k-%'UNIONSELECT `id` , `time` , `title`, 'think_infor2'   FROM `think_infor2`  WHERE `type` LIKE '$k-%'ORDER BY `time` DESCLIMIT 0,6

加了个输出字段 tbl_name 用以区分数据来源
union all 改成 union 用于避免出现重复记录。不多加了来源就重复的也不重复了

你自己写的不就可以吗?


SQL code
?



123456789

SELECT `id` , `time` , `title`, 'think_infor' as tbl_name FROM `think_infor` WHERE `type` LIKE '$k-%'UNIONSELECT `id` , `time` , `title`, 'think_in……

SELECT 不存在的字段这也可以

你是指 'think_infor' as tbl_name 这个吗?

那么你认为 SELECT `id` as iidd FROM `think_infor` 这样可以吗?
肯定可以了,不就是给个名字么

接下来就不说了

人气教程排行