欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

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

程序员文章站 2023-12-27 14:00:15
...
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` 这样可以吗?
肯定可以了,不就是给个名字么

接下来就不说了

上一篇:

下一篇: