MySQL 查询通过连接返回重复值

Question

我有以下 4 个表:

• 订单(关于订单的一般信息)
• order_items(用户添加到订单中的项目)
• 事件(关于事件的一般信息)
• campaign_items(每个事件中的项目 - 我需要此表来检查用户是否可以修改折扣和数量，或者事件是否已锁定)

我正在尝试创建一个 View ，该 View 显示包含项目(type = 1)、事件 header (type = 2) 和事件项目(类型 = 3)。

问题是，如果我在事件中有两个具有相同产品代码的产品，产品将会重复。 如何防止重复值？

这是我的 fiddle :http://sqlfiddle.com/#!9/c25a6/2

和动态链接库...

-- ----------------------------
-- 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) 

提前致谢。

Answer 1

在顶部添加 Distinct 值可防止重复值出现在您的结果中。在你的描述中，你说你想显示事件标题，但我在你的选择语句中没有看到任何这些。

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) 

如果您认为上面的 distinct 减慢了速度，您也可以这样做。

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