首页 文章

组合多个唯一的MySQL表并按列排序，＃2

提问于
浏览 次
0

我有两个独特的表，我想要做的就是组合记录并按id（这是unix时间戳）对它们进行排序：

我使用Internet上最好的匹配解决方案来编写此代码（并保持每个表的所有列都是唯一的）：Combine multiple unique MySQL tables and order by one column

$query_ticket_entries = "   SELECT * from (
        SELECT  id AS id, id AS timeentryid, userid AS timeentryuserid,
                serviceid AS timeentryserviceid, servicerateid AS timeentryservicerateid,   
                started AS timeentrystarted, ended AS timeentryended,   
                summary AS timeentrysummary, notes AS timeentrynotes,   
                NULL AS a, NULL AS b, NULL AS c, NULL AS d          
        FROM ticket_time_entries WHERE ticketid = '$ticket_id'
        UNION
        SELECT  id AS id, NULL AS aa, NULL AS bb, NULL AS cc,   
                NULL AS dd, NULL AS ee, NULL AS ff, NULL AS gg,
                NULL AS hh, id AS noteid, userid AS noteuserid,
                typeid AS notetypeid, data AS notedata  
        FROM ticket_notes WHERE ticketid = '$ticket_id'
        ) ticket_entries ORDER BY ticket_entries.id DESC
";

我得到以下结果，它缺少ticket_notes表中的所有列 .

SQL result

请帮助我找出问题并度过一个愉快的周末 . =））

-- phpMyAdmin SQL Dump
-- version 4.2.6
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 02, 2014 at 08:53 PM
-- Server version: 5.5.38-log
-- PHP Version: 5.5.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `ticket_notes`
--

CREATE TABLE IF NOT EXISTS `ticket_notes` (
  `id` int(10) NOT NULL,
  `ticketid` int(10) NOT NULL,
  `userid` int(11) NOT NULL,
  `typeid` int(2) NOT NULL,
  `data` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `ticket_time_entries`
--

CREATE TABLE IF NOT EXISTS `ticket_time_entries` (
  `id` int(10) NOT NULL,
  `ticketid` int(10) NOT NULL,
  `userid` int(10) NOT NULL,
  `serviceid` int(2) NOT NULL,
  `servicerateid` int(2) NOT NULL,
  `started` int(10) NOT NULL,
  `ended` int(10) NOT NULL,
  `summary` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `notes` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ticket_notes`
--
ALTER TABLE `ticket_notes`
 ADD UNIQUE KEY `id` (`id`);

--
-- Indexes for table `ticket_time_entries`
--
ALTER TABLE `ticket_time_entries`
 ADD UNIQUE KEY `id` (`id`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
php mysql union

1 回答

  • 1

    UNION将行结果排列在彼此之上，因此来自包含UNION的第二个或后续查询的所有列名称都不会出现 . 你确定你需要UNION而不是JOIN吗？

    $query_ticket_entries = " 
        SELECT  tte.id AS timeentryid, tte.userid AS timeentryuserid,
                serviceid AS timeentryserviceid, servicerateid AS timeentryservicerateid,   
                started AS timeentrystarted, ended AS timeentryended,   
                summary AS timeentrysummary, notes AS timeentrynotes,   
                tn.id AS noteid, tn.userid AS noteuserid,
                typeid AS notetypeid, data AS notedata         
        FROM ticket_time_entries tte LEFT JOIN ticket_notes tn
        ON tte.ticketid=tn.ticketid
        WHERE tte.ticketid = '$ticket_id'
        ORDER BY timeentryid DESC
";

    UPDATE . 好的，我得到你想要达到的目标 . 这看起来有点奇怪，但是：

    $query_ticket_entries = "   SELECT * from (
        SELECT  id AS id, id AS timeentryid, userid AS timeentryuserid,
                serviceid AS timeentryserviceid, servicerateid AS timeentryservicerateid,   
                started AS timeentrystarted, ended AS timeentryended,   
                summary AS timeentrysummary, notes AS timeentrynotes,   
                NULL AS noteid, NULL AS noteuserid, NULL AS notetypeid, NULL AS notedata          
        FROM ticket_time_entries WHERE ticketid = '$ticket_id'
        UNION
        SELECT  id AS id, NULL AS aa, NULL AS bb, NULL AS cc,   
                NULL AS dd, NULL AS ee, NULL AS ff, NULL AS gg,
                NULL AS hh, id AS noteid, userid AS noteuserid,
                typeid AS notetypeid, data AS notedata  
        FROM ticket_notes WHERE ticketid = '$ticket_id'
        ) ticket_entries ORDER BY ticket_entries.id DESC
";
    回复于

相关问题