Select with standard by in子句

Question

抱歉，如果我重新发明了轮子(和我的英语也)，也许是不可能与普通的查询，但我有“无意义的想法”，这是可能的移动块结果集顶部，我希望这个SQL转储(MySql)和更好的不确定我转换为帐户和它的货币，也许这些会更好地说明我尝试，真正的子句Group by with Order by而不是我的一杯java

DROP TABLE IF EXISTS `account`;

CREATE TABLE `account` (
  `idAccount` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idCustomer` int(10) unsigned DEFAULT NULL,
  `idCustomerNumber` varchar(6) DEFAULT NULL,
  `AccountNumber` varchar(12) DEFAULT NULL,
  `Currency` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`idAccount`)
) 

INSERT INTO `account` (`idAccount`,`idCustomer`,`idCustomerNumber`,`AccountNumber`,`Currency`) VALUES 
 (1,1,'000001','000001JPY101','JPY'),
 (2,1,'100002','100002GBP101','GBP'),
 (3,2,'003333','003333EUR201','EUR'),
 (4,1,'100002','100002GBP102','GBP'),
 (5,1,'000001','000001EUR101','EUR'),
 (6,1,'000001','000001USD101','USD'),
 (7,1,'000001','000001USD102','USD'),
 (8,1,'100002','100002EUR101','EUR'),
 (9,1,'100002','100002USD101','USD'),
 (10,2,'003333','003333EUR202','EUR'),
 (11,2,'003333','003333JPY201','JPY');

(Select * From Account Where CustomerNumber = '000001')

编辑:现在我希望这样会更好……

如何使用Group by currency和Order by AccountNumber创建从表选择，并可以创建以美元货币开头的结果集，其余行将按货币按字母顺序分组并按AccountNumber排序

要提取将按以下顺序排序的行，请执行以下命令: USD - EUR - JPY

编辑2：

我的错，真的很抱歉，真的是时候开始学习主要语言了

基本上，如果我调用SELECT * FROM Account Where idCustomerNumber = '000001' Order by Currency, AccountNumber，那么SQL解释器将返回

5,1,"000001","000001EUR101","EUR"
1,1,"000001","000001JPY101","JPY"
6,1,"000001","000001USD101","USD"
7,1,"000001","000001USD102","USD"

但现在我想以编程方式确定结果集从美元帐户开始，帐户的其余部分将按货币分组，然后按AccountNumber排序

Answer 1

还不清楚你想要什么。也许是这个？：

SELECT
    *
FROM
    account
WHERE
    idCustomerNumber = '000001'
ORDER BY
    Currency = 'USD'  DESC
  , Currency
  , AccountNumber ;

Answer 2

我认为这应该会对你有所帮助：

 SELECT * FROM account
 WHERE idCustomerNumber = '000001'
 ORDER BY AccountNumber, CASE WHEN Currency = 'USD' THEN 0 WHEN Currency = 'EUR' THEN 1 WHEN Currency = 'JPY' THEN 2 ELSE 0 END

通常，当你尝试分组时，你需要考虑如何处理你组合的列，对于分组，你需要使用汇总值，例如:假设你有一个Amount列：

 SELECT AccountNumber, Currency, SUM(Amount) AS TotalAmount FROM account
 WHERE idCustomerNumber = '000001'
 GROUP BY AccountNumber, Currency
 ORDER BY AccountNumber, CASE WHEN Currency = 'USD' THEN 0 WHEN Currency = 'EUR' THEN 1 WHEN Currency = 'JPY' THEN 2 ELSE 0 END

至于按美元、欧元、日元等排序，最好在CurrencySortOrder表中定义，这样您就可以添加更多的值，而不必更改所有查询等等。