我有以下4表：
订单（有关订单的一般信息）
order_items（用户已添加到订单中的项目）
广告系列（有关广告系列的一般信息）
campaign_items（每个广告系列中的项目-我需要此表来检查用户是否可以修改折扣和数量，或者广告系列是否已锁定）
我正在尝试创建一个视图，以显示包含商品（类型= 1），广告系列标题（类型= 2）和广告系列项目（类型= 3）的订单。
有一个问题，如果我在广告系列中有两个产品具有相同的产品代码，则产品将被复制。如何防止重复的值？
这是我的小提琴：http://sqlfiddle.com/#!9/c25a6/2
和DLL ...
-- ----------------------------
-- Table structure for campaign_items
-- ----------------------------
DROP TABLE IF EXISTS `campaign_items`;
CREATE TABLE `campaign_items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`campaignId` int(11) NOT NULL,
`productId` int(11) NOT NULL,
`price` decimal(10,2) DEFAULT NULL,
`quantity` int(11) NOT NULL DEFAULT '0',
`qtylocked` tinyint(1) NOT NULL DEFAULT '0',
`discount` decimal(10,2) DEFAULT '0.00',
`discountlocked` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `campaign` (`campaignId`) USING BTREE,
KEY `product` (`productId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1406 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of campaign_items
-- ----------------------------
INSERT INTO `campaign_items` VALUES ('1404', '52', '103580', null, '2', '1', '0.00', '1');
INSERT INTO `campaign_items` VALUES ('1405', '52', '103580', null, '1', '1', '100.00', '1');
-- ----------------------------
-- Table structure for campaigns
-- ----------------------------
DROP TABLE IF EXISTS `campaigns`;
CREATE TABLE `campaigns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` int(20) DEFAULT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of campaigns
-- ----------------------------
INSERT INTO `campaigns` VALUES ('52', null, 'Test campaign');
-- ----------------------------
-- Table structure for order_items
-- ----------------------------
DROP TABLE IF EXISTS `order_items`;
CREATE TABLE `order_items` (
`id` int(100) NOT NULL AUTO_INCREMENT,
`type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=product, 2=campaign, 3=campaign item',
`orderId` int(6) NOT NULL,
`campaignId` int(11) DEFAULT NULL,
`campaignUniqueId` varchar(100) DEFAULT NULL,
`productId` varchar(50) DEFAULT NULL,
`discount` decimal(10,2) NOT NULL,
`quantity` int(5) NOT NULL DEFAULT '1',
`campaignquantity` int(5) DEFAULT NULL,
`unitprice` decimal(10,5) NOT NULL,
`alv` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`,`orderId`)
) ENGINE=InnoDB AUTO_INCREMENT=144677 DEFAULT CHARSET=utf8;
INSERT INTO `order_items` VALUES ('144657', '2', '806035', '52', '57b5a3a686780', null, '0.00', '1', null, '0.00000', '0.00');
INSERT INTO `order_items` VALUES ('144658', '2', '806035', '47', '57b5955edbc34', '180150', '0.00', '5', null, '0.00000', '0.00');
INSERT INTO `order_items` VALUES ('144659', '3', '806035', '52', '57b5a3a686780', '103580', '100.00', '1', '1', '5.30000', '24.00');
INSERT INTO `order_items` VALUES ('144660', '3', '806035', '52', '57b5a3a686780', '103580', '0.00', '2', '1', '5.30000', '24.00');
INSERT INTO `order_items` VALUES ('144661', '3', '806035', '47', '57b5955edbc34', '104016', '0.00', '6', '5', '23.00000', '14.00');
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customerId` int(11) NOT NULL,
PRIMARY KEY (`id`,`customerId`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=806769 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('806035', '3221');
而我尝试查询...
SELECT
items.id AS rowid,
orders.id,
orders.customerId,
items.type,
items.campaignId,
items.productId,
items.discount,
items.quantity,
items.campaignquantity,
round(items.unitprice, 2) AS priceWithoutDiscount,
round(items.unitprice * (1 - items.discount / 100), 2) as priceUnit,
round(items.unitprice * (1 - items.discount / 100) * (1 + items.alv / 100), 2) as priceUnitVat,
case items.type
when '1' THEN
round(items.unitprice * (1 - items.discount / 100) * items.quantity, 2)
when '2' THEN
round(items.unitprice * (1 - items.discount / 100) * items.quantity, 2)
when '3' THEN
round(items.campaignquantity * items.unitprice * (1 - items.discount / 100) * items.quantity, 2)
end as priceTotal,
case items.type
when '1' THEN
round(items.unitprice * (1 - items.discount / 100) * items.quantity * (1 + items.alv / 100), 2)
when '2' THEN
round(items.unitprice * (1 - items.discount / 100) * items.quantity * (1 + items.alv / 100), 2)
when '3' THEN
round(items.campaignquantity * items.unitprice * (1 - items.discount / 100) * items.quantity * (1 + items.alv / 100), 2)
end as priceTotalVat,
items.campaignUniqueId
FROM
orders orders
LEFT JOIN order_items items ON orders.id = items.orderId
LEFT JOIN campaigns campaigns ON campaigns.id = items.campaignId
LEFT JOIN campaign_items campaign_items ON (campaign_items.campaignId = campaigns.id AND items.productId = campaign_items.productId)
提前致谢。
最佳答案
在顶部添加Distinct值可防止重复值出现在结果中。在说明中，您说要显示广告系列标题，但在select语句中没有看到任何标题。
SELECT distinct
items.id AS rowid,
orders.id,
orders.customerId,
items.type,
items.campaignId,
items.productId,
items.discount,
items.quantity,
items.campaignquantity,
round(items.unitprice, 2) AS priceWithoutDiscount,
round(items.unitprice * (1 - items.discount / 100), 2) as priceUnit,
round(items.unitprice * (1 - items.discount / 100) * (1 + items.alv / 100), 2) as priceUnitVat,
case items.type
when '1' THEN
round(items.unitprice * (1 - items.discount / 100) * items.quantity, 2)
when '2' THEN
round(items.unitprice * (1 - items.discount / 100) * items.quantity, 2)
when '3' THEN
round(items.campaignquantity * items.unitprice * (1 - items.discount / 100) * items.quantity, 2)
end as priceTotal,
case items.type
when '1' THEN
round(items.unitprice * (1 - items.discount / 100) * items.quantity * (1 + items.alv / 100), 2)
when '2' THEN
round(items.unitprice * (1 - items.discount / 100) * items.quantity * (1 + items.alv / 100), 2)
when '3' THEN
round(items.campaignquantity * items.unitprice * (1 - items.discount / 100) * items.quantity * (1 + items.alv / 100), 2)
end as priceTotalVat,
items.campaignUniqueId
FROM
orders orders
LEFT JOIN order_items items ON orders.id = items.orderId
LEFT JOIN campaigns campaigns ON campaigns.id = items.campaignId
LEFT JOIN campaign_items campaign_items ON (campaign_items.campaignId = campaigns.id AND items.productId = campaign_items.productId)
如果您认为上述区别正在减慢速度，您也可以这样做。
SELECT DISTINCT * from (
SELECT
items.id AS rowid,
orders.id,
orders.customerId,
items.type,
items.campaignId,
items.productId,
items.discount,
items.quantity,
items.campaignquantity,
round(items.unitprice, 2) AS priceWithoutDiscount,
round(items.unitprice * (1 - items.discount / 100), 2) as priceUnit,
round(items.unitprice * (1 - items.discount / 100) * (1 + items.alv / 100), 2) as priceUnitVat,
case items.type
when '1' THEN
round(items.unitprice * (1 - items.discount / 100) * items.quantity, 2)
when '2' THEN
round(items.unitprice * (1 - items.discount / 100) * items.quantity, 2)
when '3' THEN
round(items.campaignquantity * items.unitprice * (1 - items.discount / 100) * items.quantity, 2)
end as priceTotal,
case items.type
when '1' THEN
round(items.unitprice * (1 - items.discount / 100) * items.quantity * (1 + items.alv / 100), 2)
when '2' THEN
round(items.unitprice * (1 - items.discount / 100) * items.quantity * (1 + items.alv / 100), 2)
when '3' THEN
round(items.campaignquantity * items.unitprice * (1 - items.discount / 100) * items.quantity * (1 + items.alv / 100), 2)
end as priceTotalVat,
items.campaignUniqueId
FROM
orders orders
LEFT JOIN order_items items ON orders.id = items.orderId
LEFT JOIN campaigns campaigns ON campaigns.id = items.campaignId
LEFT JOIN campaign_items campaign_items ON (campaign_items.campaignId = campaigns.id AND items.productId = campaign_items.productId)) Distict_Table
关于mysql - MySQL查询返回带有联接的重复值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39018772/