求大神帮忙指点指点。

tk12000 2013-10-30 06:34:16

现在碰到如下问题、需要查询几个平台下的全部店铺（一个平台下有若干个店铺）、使用如下语句。



SELECT

	`paipai_shop`.`sellerUin` AS `shopId`,

	`paipai_shop`.`shopName` AS `shopName`

FROM

	`paipai_shop`

UNION ALL

	SELECT

		`dangdang_shop`.`id` AS `shopId`,

		`dangdang_shop`.`shopName` AS `shopName`

	FROM

		`dangdang_shop`

	UNION ALL

		SELECT

			`vjia_shop`.`id` AS `shopId`,

			`vjia_shop`.`shopName` AS `shopName`

		FROM

			`vjia_shop`

		UNION ALL

			SELECT

				`amazon_shop`.`id` AS `shopId`,

				`amazon_shop`.`shopName` AS `shopName`

			FROM

				`amazon_shop`

			UNION ALL

				SELECT

					`taobao_shop`.`shopId` AS `shopId`,

					`taobao_shop`.`nick` AS `shopName`

				FROM

					`taobao_shop`

				UNION ALL

					SELECT

						`jingd_shop`.`shopId` AS `shopId`,

						`jingd_shop`.`shopName` AS `shopName`

					FROM

						`jingd_shop`

					UNION ALL

						SELECT

							`yhd_shop`.`shopId` AS `shopId`,

							`yhd_shop`.`shopName` AS `shopName`

						FROM

							`yhd_shop`

查询出来后、因为不同平台下的店铺可能有相同的 shopId 、导致页面上相同shopid的店铺只显示一个条数据。求有无解决方法。

我曾经尝试给不同平台下的店铺shopid 后面拼接数字譬如：`yhd_shop`.`shopId`+0 AS `shopId` 结果没有用。哪位大神给个解决方法？

...全文

169 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

發糞塗牆 2013-10-31

打赏
举报

shopid和shopname，原则上只要有一个不相同，就不会重复或者合并。或者你可以直接用shopid+shopname这样来显示

-Tracy-McGrady- 2013-10-30

打赏
举报

如果是sql server的话，使用了union all就不会出现你的这个问题啊，是不是其他哪里出错了？

LongRui888 2013-10-30

打赏
举报

别加0，加了0等于没加，加100试试：

SELECT
	`paipai_shop`.`sellerUin` AS `shopId`,
	`paipai_shop`.`shopName` AS `shopName`
FROM
	`paipai_shop`
UNION ALL
SELECT
	`dangdang_shop`.`id` AS `shopId`,
	`dangdang_shop`.`shopName` AS `shopName`
FROM
	`dangdang_shop`
UNION ALL
SELECT
	`vjia_shop`.`id` AS `shopId`,
	`vjia_shop`.`shopName` AS `shopName`
FROM
	`vjia_shop`
UNION ALL
SELECT
	`amazon_shop`.`id` AS `shopId`,
	`amazon_shop`.`shopName` AS `shopName`
FROM
	`amazon_shop`
UNION ALL
SELECT
	`taobao_shop`.`shopId` AS `shopId`,
	`taobao_shop`.`nick` AS `shopName`
FROM
	`taobao_shop`
UNION ALL
SELECT
	`jingd_shop`.`shopId` AS `shopId`,
	`jingd_shop`.`shopName` AS `shopName`
FROM
	`jingd_shop`
UNION ALL
SELECT
	`yhd_shop`.`shopId`+100 AS `shopId`,
	`yhd_shop`.`shopName` AS `shopName`
FROM
	`yhd_shop`

shoppo0505 2013-10-30

打赏
举报

你都UNION ALL了怎么还会出现不显示的问题呢？应该是之后的程序有问题。要不你试试：


SELECT    
`paipai_shop`.`sellerUin` AS `shopId`,     
`paipai_shop`.`shopName` AS `shopName`，
'paipai_shop'
FROM    `paipai_shop` 
UNION ALL    
SELECT        
`dangdang_shop`.`id` AS `shopId`,         
`dangdang_shop`.`shopName` AS `shopName` `，
'dangdang_shop'    
FROM        `dangdang_shop`     
UNION ALL

。。。