如何更改SQL腳本，使帶有金額的列右對齊-有解無憂

請幫我更改請求，使帶有金額的列右對齊？它們被標記為“金錢”。我將使用動態 SQL，因此欄位會發生變化。對我而言，重要的是任何未標有貨幣標簽的欄位都向右移動，而其他所有欄位都居中。我正在使用這個腳本。下面是我期望的結果。

  insert into #a 
  Values (1, 'GB', 2000),
         (2, 'DE', 170),
         (3, 'PT', 960)


    DECLARE @Values             NVARCHAR(MAX)

    select @Values = CAST((SELECT ROW_NUMBER() OVER(ORDER BY UserID)  AS 'td','',
                        (SELECT CONCAT('https://backend.com/User/UserDetails.aspx?UserID=',UserId) AS [@href],UserID FOR XML PATH('a'),TYPE) AS 'td',''
                       ,Country AS 'td',''
                       ,TotalDeposits AS 'money',''
    FROM  #a
    FOR XML PATH('tr'),ELEMENTS ) AS NVARCHAR(MAX))
    
    select '<html><body><table border=1 cellpadding=10 style="border-collapse:collapse;">'      @Values   '</table></body></html>'

<html>
  <body>
    <table border=1 cellpadding=10 style="border-collapse:collapse;">
      <tr align="center" valign="center">
        <td>1</td>
        <td>
          <a href="https://backend.com/User/UserDetails.aspx?UserID=1">
            <UserID>1</UserID>
          </a>
        </td>
        <td>GB</td>
        <td style="text-align:right;vertical-align:middle;">2000</td>
      </tr>
      <tr align="center" valign="center">
        <td>2</td>
        <td>
          <a href="https://backend.com/User/UserDetails.aspx?UserID=2">
            <UserID>2</UserID>
          </a>
        </td>
        <td>DE</td>
        <td style="text-align:right;vertical-align:middle;">170</td>
      </tr>
      <tr align="center" valign="center">
        <td>3</td>
        <td>
          <a href="https://backend.com/User/UserDetails.aspx?UserID=3">
            <UserID>3</UserID>
          </a>
        </td>
        <td>PT</td>
        <td style="text-align:right;vertical-align:middle;">960</td>
      </tr>
    </table>
  </body>
</html>

uj5u.com熱心網友回復：

對于 XQuery 來說，這是一項簡單的任務。

好處：

沒有字串連接。
不用擔心 NULL 值。
非常容易創建，非常容易維護。
UI 樣式是通過 CSS 控制的。

查詢陳述句

-- DDL and sample data population, start
DECLARE @tbl TABLE (UserID INT PRIMARY KEY, Country CHAR(2), TotalDeposits MONEY, IsMoney BIT);
INSERT INTO @tbl (UserID, Country, TotalDeposits, IsMoney) VALUES
(1, 'GB', 2000, 0),
(2, 'DE', 170, 1),
(3, 'PT', 960, 0)
-- DDL and sample data population, end

DECLARE @xhtmlBody XML
   , @body NVARCHAR(MAX)
   , @tableCaption VARCHAR(30) = 'Users list';

SET @xhtmlBody = (SELECT (
SELECT * FROM @tbl FOR XML PATH('row'), TYPE, ROOT('root'))
.query('<html><head>
            <meta charset="utf-8"/>
            (: including embedded CSS styling :)
            <style>
            table <![CDATA[ {border-collapse: collapse;  width: 300px;} ]]>
            th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
            th, td <![CDATA[ { text-align: left; padding: 8px;} ]]>
            tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
            #green <![CDATA[ {background-color: lightgreen;} ]]>
         </style>
         </head>
         <body>
<table border="1" cellpadding="10" style="border-collapse:collapse;">
   <caption><h2>{sql:variable("@tableCaption")}</h2></caption>
   <thead>
      <tr>
        <th>No.</th>
        <th>UserID</th>
        <th>Country</th>
        <th>TotalDeposits</th>
      </tr>
   </thead>
   <tbody>
{
    for $row in /root/row
    let $pos := count(root/row[. << $row])   1
    return <tr align="center" valign="center">
            <td>{$pos}</td>
            <td><a href="{concat(''https://backend.com/User/UserDetails.aspx?UserID='',data($row/UserID[1]))}">
                        <UserID>{data($row/UserID)}</UserID>
                    </a></td>
            <td>{data($row/Country)}</td>
            <td style="{concat(if (data($row/IsMoney="1")) then "text-align:right;"
                else "text-align:left;", "vertical-align:middle;")}">{data($row/TotalDeposits)}</td>
        </tr>
}
</tbody></table></body></html>'));

SELECT @xhtmlBody;

SET @body = CAST(@xhtmlBody AS NVARCHAR(MAX));

輸出

<html>
  <head>
    <meta charset="utf-8" />
    <style>
            table  {border-collapse: collapse;  width: 300px;} 
            th  {background-color: #4CAF50; color: white;} 
            th, td  { text-align: left; padding: 8px;} 
            tr:nth-child(even)  {background-color: #f2f2f2;} 
            #green  {background-color: lightgreen;} 
         </style>
  </head>
  <body>
    <table border="1" cellpadding="10" style="border-collapse:collapse;">
      <caption>
        <h2>Users list</h2>
      </caption>
      <thead>
        <tr>
          <th>No.</th>
          <th>UserID</th>
          <th>Country</th>
          <th>TotalDeposits</th>
        </tr>
      </thead>
      <tbody>
        <tr align="center" valign="center">
          <td>1</td>
          <td>
            <a href="https://backend.com/User/UserDetails.aspx?UserID=1">
              <UserID>1</UserID>
            </a>
          </td>
          <td>GB</td>
          <td style="text-align:left;vertical-align:middle;">2000.0000</td>
        </tr>
        <tr align="center" valign="center">
          <td>2</td>
          <td>
            <a href="https://backend.com/User/UserDetails.aspx?UserID=2">
              <UserID>2</UserID>
            </a>
          </td>
          <td>DE</td>
          <td style="text-align:right;vertical-align:middle;">170.0000</td>
        </tr>
        <tr align="center" valign="center">
          <td>3</td>
          <td>
            <a href="https://backend.com/User/UserDetails.aspx?UserID=3">
              <UserID>3</UserID>
            </a>
          </td>
          <td>PT</td>
          <td style="text-align:left;vertical-align:middle;">960.0000</td>
        </tr>
      </tbody>
    </table>
  </body>
</html>

轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/372097.html

標籤：sql-server xml

上一篇：SQL資料從文本匯入將所有欄位插入單列

下一篇：如何獲取適合日期范圍的行以及每個ID給定日期范圍之前的最新行？