当前位置： 文江博客话题详情

枚举列上的 SQL 连接返回所有可能的值

发布于 2024-11-25 07:25:23 字数 6272 浏览 2 评论 0原文

我有一个 SQL 查询，它选择一个枚举列并执行联接。以下是涉及的数据库表和相关数据：

OFFER TABLE

-- 
-- Table structure for table `offer`
-- 

CREATE TABLE `offer` (
  `id` int(11) NOT NULL auto_increment,
  `companyID` int(11) NOT NULL,
  `categoryID` int(11) NOT NULL,
  `dateAdded` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `details` text NOT NULL,
  `amount` decimal(11,0) NOT NULL,
  `maxAmount` decimal(11,0) NOT NULL default '0',
  `dateExpires` int(11) NOT NULL default '0',
  `active` enum('YES','NO') NOT NULL,
  `featured` enum('YES','NO') NOT NULL default 'NO',
  `adType` enum('OFFER','URL') NOT NULL default 'OFFER',
  `URL` varchar(255) NOT NULL,
  `address1` varchar(50) NOT NULL,
  `address2` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  `zip` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `companyID` (`companyID`),
  KEY `categoryID` (`categoryID`),
  KEY `zip` (`zip`)
) ENGINE=MyISAM AUTO_INCREMENT=66 DEFAULT CHARSET=utf8 AUTO_INCREMENT=66 ;

-- 
-- Dumping data for table `offer`
-- 

INSERT INTO `offer` (`id`, `companyID`, `categoryID`, `dateAdded`, `details`, `amount`, `maxAmount`, `dateExpires`, `active`, `featured`, `adType`, `URL`, `address1`, `address2`, `city`, `state`, `zip`) VALUES 
(24, 10, 6, '2011-05-19 14:55:54', 'Computer Software & Books', 25, 0, 0, 'NO', 'NO', 'OFFER', '', '912 N. Avenue 57', '', 'Los Angeles', 'CA', '90043')

PAYMENTS TABLE

-- 
-- Table structure for table `payments`
-- 

CREATE TABLE `payments` (
  `id` int(11) NOT NULL auto_increment,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `offerID` int(11) NOT NULL,
  `amount` decimal(11,2) NOT NULL,
  `memberID` int(11) NOT NULL,
  `validationNumber` varchar(20) NOT NULL,
  `status` enum('NEW','USED') NOT NULL default 'NEW',
  `dateRedeemed` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `validationNumber` (`validationNumber`),
  KEY `offerID` (`offerID`),
  KEY `memberID` (`memberID`)
) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 AUTO_INCREMENT=33 ;

-- 
-- Dumping data for table `payments`
-- 

INSERT INTO `payments` (`id`, `date`, `offerID`, `amount`, `memberID`, `validationNumber`, `status`, `dateRedeemed`) VALUES
(1, '2011-03-28 16:33:24', 24, 11.00, 8, '1A2B3-1', 'NEW', '2011-07-18 15:00:41'),
(2, '2011-04-12 12:47:58', 16, 81.10, 8, '2C3D4-2', 'NEW', '0000-00-00 00:00:00'),
(3, '2011-05-19 19:50:58', 24, 22.15, 14, 'ABCDE-3', 'USED', '2011-07-18 15:03:00'),
(4, '2011-05-19 19:50:58', 24, 44.30, 5, 'FGHIJK-4', 'USED', '2011-07-18 15:03:45');

TRANSACTIONS TABLE

-- 
-- Table structure for table `transactions`
-- 

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL auto_increment,
  `companyID` int(11) NOT NULL,
  `offerID` int(11) NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `amount` decimal(10,2) NOT NULL,
  `type` enum('CREDIT','DEBIT') NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `companyID` (`companyID`,`offerID`)
) ENGINE=MyISAM AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 AUTO_INCREMENT=38 ;

-- 
-- Dumping data for table `transactions`
-- 

INSERT INTO `transactions` (`id`, `companyID`, `offerID`, `date`, `amount`, `type`) VALUES
(1, 10, 24, '2011-05-20 11:25:35', 11.00, 'CREDIT'),
(2, 10, 24, '2011-05-20 11:25:35', 22.15, 'CREDIT'),
(3, 10, 24, '2011-05-20 11:26:11', 44.30, 'CREDIT'),
(4, 10, 0, '2011-05-20 14:55:13', -3.50, 'DEBIT'),
(5, 10, 0, '2011-05-20 14:59:50', -5.00, 'DEBIT'),
(6, 10, 0, '2011-05-20 15:45:29', -4.50, 'DEBIT'),
(36, 10, 0, '2011-07-08 15:03:06', -10.00, 'DEBIT'),
(37, 10, 0, '2011-07-08 15:03:19', -2.45, 'DEBIT');

这是查询：

SELECT DISTINCT t.*, o.amount AS discount, p.status FROM transactions t 
            LEFT JOIN offer o ON o.id = t.offerID 
            LEFT JOIN payments p ON p.offerId = t.offerID
            WHERE t.companyID = '10' ORDER BY date ASC

最后，这是结果集：

id  companyID   offerID     date    amount  type    discount    status
1   10  24  2011-05-20 11:25:35     11.00   CREDIT  25  NEW
1   10  24  2011-05-20 11:25:35     11.00   CREDIT  25  USED
2   10  24  2011-05-20 11:25:35     22.15   CREDIT  25  NEW
2   10  24  2011-05-20 11:25:35     22.15   CREDIT  25  USED
3   10  24  2011-05-20 11:26:11     44.30   CREDIT  25  USED
3   10  24  2011-05-20 11:26:11     44.30   CREDIT  25  NEW
4   10  0   2011-05-20 14:55:13     -3.50   DEBIT   NULL    NULL
5   10  0   2011-05-20 14:59:50     -5.00   DEBIT   NULL    NULL
6   10  0   2011-05-20 15:45:29     -4.50   DEBIT   NULL    NULL
36  10  0   2011-07-08 15:03:06     -10.00  DEBIT   NULL    NULL
37  10  0   2011-07-08 15:03:19     -2.45   DEBIT   NULL    NULL

问题是 CREDIT 结果出现两次，同时包含 NEW 和 USED 枚举值。我只想要付款行设置的枚举值。如果有人能帮助我理解为什么这个查询不起作用以及如何修复它，我将不胜感激。提前致谢！

@michael、@chopikadze：

我已将memberID 添加到交易表中。 我这样修改了我的查询：

SELECT t. * , o.amount AS discount, p.status
FROM transactions t
LEFT JOIN offer o ON o.id = t.offerID
LEFT JOIN payments p ON p.offerId = t.offerID
LEFT JOIN members m ON m.id = t.memberID
WHERE t.companyID = '10'
ORDER BY date ASC

这是我的新结果集：

id  companyID   offerID     memberID    date    amount  type    discount    status
1   10  24  8   2011-05-20 11:25:35     11.00   CREDIT  25  NEW
1   10  24  8   2011-05-20 11:25:35     11.00   CREDIT  25  USED
1   10  24  8   2011-05-20 11:25:35     11.00   CREDIT  25  USED
2   10  24  14  2011-05-20 11:25:35     22.15   CREDIT  25  NEW
2   10  24  14  2011-05-20 11:25:35     22.15   CREDIT  25  USED
2   10  24  14  2011-05-20 11:25:35     22.15   CREDIT  25  USED
3   10  24  5   2011-05-20 11:26:11     44.30   CREDIT  25  NEW
3   10  24  5   2011-05-20 11:26:11     44.30   CREDIT  25  USED
3   10  24  5   2011-05-20 11:26:11     44.30   CREDIT  25  USED
4   10  0   0   2011-05-20 14:55:13     -3.50   DEBIT   NULL    NULL
5   10  0   0   2011-05-20 14:59:50     -5.00   DEBIT   NULL    NULL
6   10  0   0   2011-05-20 15:45:29     -4.50   DEBIT   NULL    NULL
36  10  0   0   2011-07-08 15:03:06     -10.00  DEBIT   NULL    NULL
37  10  0   0   2011-07-08 15:03:19     -2.45   DEBIT   NULL    NULL

似乎添加memberID 导致了更多重复的结果。我做错了什么？请纠正我这一点。谢谢。

原文

I have a SQL query that selects an enum column and performs a join. Here are the DB tables involved and relevant data:

OFFER TABLE

-- 
-- Table structure for table `offer`
-- 

CREATE TABLE `offer` (
  `id` int(11) NOT NULL auto_increment,
  `companyID` int(11) NOT NULL,
  `categoryID` int(11) NOT NULL,
  `dateAdded` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `details` text NOT NULL,
  `amount` decimal(11,0) NOT NULL,
  `maxAmount` decimal(11,0) NOT NULL default '0',
  `dateExpires` int(11) NOT NULL default '0',
  `active` enum('YES','NO') NOT NULL,
  `featured` enum('YES','NO') NOT NULL default 'NO',
  `adType` enum('OFFER','URL') NOT NULL default 'OFFER',
  `URL` varchar(255) NOT NULL,
  `address1` varchar(50) NOT NULL,
  `address2` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  `zip` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `companyID` (`companyID`),
  KEY `categoryID` (`categoryID`),
  KEY `zip` (`zip`)
) ENGINE=MyISAM AUTO_INCREMENT=66 DEFAULT CHARSET=utf8 AUTO_INCREMENT=66 ;

-- 
-- Dumping data for table `offer`
-- 

INSERT INTO `offer` (`id`, `companyID`, `categoryID`, `dateAdded`, `details`, `amount`, `maxAmount`, `dateExpires`, `active`, `featured`, `adType`, `URL`, `address1`, `address2`, `city`, `state`, `zip`) VALUES 
(24, 10, 6, '2011-05-19 14:55:54', 'Computer Software & Books', 25, 0, 0, 'NO', 'NO', 'OFFER', '', '912 N. Avenue 57', '', 'Los Angeles', 'CA', '90043')

PAYMENTS TABLE

-- 
-- Table structure for table `payments`
-- 

CREATE TABLE `payments` (
  `id` int(11) NOT NULL auto_increment,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `offerID` int(11) NOT NULL,
  `amount` decimal(11,2) NOT NULL,
  `memberID` int(11) NOT NULL,
  `validationNumber` varchar(20) NOT NULL,
  `status` enum('NEW','USED') NOT NULL default 'NEW',
  `dateRedeemed` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `validationNumber` (`validationNumber`),
  KEY `offerID` (`offerID`),
  KEY `memberID` (`memberID`)
) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 AUTO_INCREMENT=33 ;

-- 
-- Dumping data for table `payments`
-- 

INSERT INTO `payments` (`id`, `date`, `offerID`, `amount`, `memberID`, `validationNumber`, `status`, `dateRedeemed`) VALUES
(1, '2011-03-28 16:33:24', 24, 11.00, 8, '1A2B3-1', 'NEW', '2011-07-18 15:00:41'),
(2, '2011-04-12 12:47:58', 16, 81.10, 8, '2C3D4-2', 'NEW', '0000-00-00 00:00:00'),
(3, '2011-05-19 19:50:58', 24, 22.15, 14, 'ABCDE-3', 'USED', '2011-07-18 15:03:00'),
(4, '2011-05-19 19:50:58', 24, 44.30, 5, 'FGHIJK-4', 'USED', '2011-07-18 15:03:45');

TRANSACTIONS TABLE

-- 
-- Table structure for table `transactions`
-- 

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL auto_increment,
  `companyID` int(11) NOT NULL,
  `offerID` int(11) NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `amount` decimal(10,2) NOT NULL,
  `type` enum('CREDIT','DEBIT') NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `companyID` (`companyID`,`offerID`)
) ENGINE=MyISAM AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 AUTO_INCREMENT=38 ;

-- 
-- Dumping data for table `transactions`
-- 

INSERT INTO `transactions` (`id`, `companyID`, `offerID`, `date`, `amount`, `type`) VALUES
(1, 10, 24, '2011-05-20 11:25:35', 11.00, 'CREDIT'),
(2, 10, 24, '2011-05-20 11:25:35', 22.15, 'CREDIT'),
(3, 10, 24, '2011-05-20 11:26:11', 44.30, 'CREDIT'),
(4, 10, 0, '2011-05-20 14:55:13', -3.50, 'DEBIT'),
(5, 10, 0, '2011-05-20 14:59:50', -5.00, 'DEBIT'),
(6, 10, 0, '2011-05-20 15:45:29', -4.50, 'DEBIT'),
(36, 10, 0, '2011-07-08 15:03:06', -10.00, 'DEBIT'),
(37, 10, 0, '2011-07-08 15:03:19', -2.45, 'DEBIT');

Here's the query:

SELECT DISTINCT t.*, o.amount AS discount, p.status FROM transactions t 
            LEFT JOIN offer o ON o.id = t.offerID 
            LEFT JOIN payments p ON p.offerId = t.offerID
            WHERE t.companyID = '10' ORDER BY date ASC

Finally, here's the result set:

id  companyID   offerID     date    amount  type    discount    status
1   10  24  2011-05-20 11:25:35     11.00   CREDIT  25  NEW
1   10  24  2011-05-20 11:25:35     11.00   CREDIT  25  USED
2   10  24  2011-05-20 11:25:35     22.15   CREDIT  25  NEW
2   10  24  2011-05-20 11:25:35     22.15   CREDIT  25  USED
3   10  24  2011-05-20 11:26:11     44.30   CREDIT  25  USED
3   10  24  2011-05-20 11:26:11     44.30   CREDIT  25  NEW
4   10  0   2011-05-20 14:55:13     -3.50   DEBIT   NULL    NULL
5   10  0   2011-05-20 14:59:50     -5.00   DEBIT   NULL    NULL
6   10  0   2011-05-20 15:45:29     -4.50   DEBIT   NULL    NULL
36  10  0   2011-07-08 15:03:06     -10.00  DEBIT   NULL    NULL
37  10  0   2011-07-08 15:03:19     -2.45   DEBIT   NULL    NULL

The problem is that the CREDIT results appear twice with both NEW and USED enum values. I only want the enum value that the payments row is set to. I would appreciate it if anyone could help me understand why this query is not working, and how to fix it. Thanks in advance!

@michael, @chopikadze:

I have added memberID to the transactions table.
I have revised my query thus:

SELECT t. * , o.amount AS discount, p.status
FROM transactions t
LEFT JOIN offer o ON o.id = t.offerID
LEFT JOIN payments p ON p.offerId = t.offerID
LEFT JOIN members m ON m.id = t.memberID
WHERE t.companyID = '10'
ORDER BY date ASC

And this is my new result set:

id  companyID   offerID     memberID    date    amount  type    discount    status
1   10  24  8   2011-05-20 11:25:35     11.00   CREDIT  25  NEW
1   10  24  8   2011-05-20 11:25:35     11.00   CREDIT  25  USED
1   10  24  8   2011-05-20 11:25:35     11.00   CREDIT  25  USED
2   10  24  14  2011-05-20 11:25:35     22.15   CREDIT  25  NEW
2   10  24  14  2011-05-20 11:25:35     22.15   CREDIT  25  USED
2   10  24  14  2011-05-20 11:25:35     22.15   CREDIT  25  USED
3   10  24  5   2011-05-20 11:26:11     44.30   CREDIT  25  NEW
3   10  24  5   2011-05-20 11:26:11     44.30   CREDIT  25  USED
3   10  24  5   2011-05-20 11:26:11     44.30   CREDIT  25  USED
4   10  0   0   2011-05-20 14:55:13     -3.50   DEBIT   NULL    NULL
5   10  0   0   2011-05-20 14:59:50     -5.00   DEBIT   NULL    NULL
6   10  0   0   2011-05-20 15:45:29     -4.50   DEBIT   NULL    NULL
36  10  0   0   2011-07-08 15:03:06     -10.00  DEBIT   NULL    NULL
37  10  0   0   2011-07-08 15:03:19     -2.45   DEBIT   NULL    NULL

It seems that adding memberID has resulted in more duplicate results. What did I do wrong? Please correct me on this one guys. Thanks.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

国际总奸 2024-12-02 07:25:23
SELECT t. * , o.amount AS discount, p.status
FROM transactions t
    LEFT JOIN offer o ON o.id = t.offerID
    LEFT JOIN payments p ON p.offerId = t.offerID and p.memberID = t.memberID
WHERE t.companyID = '10'
ORDER BY date ASC

正如我所说，你不仅应该

在交易表中添加memberID字段

，而且

不仅在 OfferID 上加入交易和付款，还在memberID 上加入交易和付款

SELECT t. * , o.amount AS discount, p.status
FROM transactions t
    LEFT JOIN offer o ON o.id = t.offerID
    LEFT JOIN payments p ON p.offerId = t.offerID and p.memberID = t.memberID
WHERE t.companyID = '10'
ORDER BY date ASC

As I said, you should not only

add memberID field to transaction table

but also

join transactions and payments not only on OfferID, but on memberID too

回复 原文
~没有更多了~

关于作者

此刻的回忆

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

alipaysp_snBf0MSZIv

文章 0 评论 0

梦断已成空

文章 0 评论 0

瞎闹

文章 0 评论 0

凯凯我们等你回来

文章 0 评论 0

寄意

文章 0 评论 0

似梦非梦

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文